Home 2011 Archives for February 2011

Archives for February 2011

February 27, 2011 Leave a Comment

Creating Combination Charts in Excel

A combination chart in Excel is not a single chart type. Rather, you create combination charts by combining two or more complementary chart types. The most common combination is a Column type for one series and a Line type chart for the second series. Combination Charts, in general, make it easier to spot or highlight the trends in your data.

Combination Chart

Excel Combination Chart

 Creating a Combination Chart

  1. Create a standard chart for your data.
  2. Select one series and then choose a different chart type for that series.
  3. Make formatting choices as necessary.

Adding a Secondary Axis for Data

When you are comparing data series that have a wide “gap” between them – e.g. comparing actual sales in a month (large number) – to Profit % in each month (small number), you will want to create a Secondary Vertical (Value) Axis in your Combination Chart.

Begin by selecting the series that you want to plot and Right Mouse Click to Format the Series on a Secondary Vertical Axis. I recommend that you add in Axis Titles for both the Primary and Secondary Vertical Axes.

Formatting Charts in Excel 2007 and 2010

Charting in Excel – beginning with Version 2007 – now offers quite a few professional graphic design elements. In this video lesson, I show you how to customize the presentation of your graphical image. You can make these changes with just a few mouse clicks! It is definitely worthwhile to learn how to improve the visual impact that your Combination Charts make.

Learn how to “Master Excel in Minutes – Not Months!”

Follow this link to watch this Excel Video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Subscribe to or watch my – free – video podcast on iTunes.

Creating Combination Charts in Excel [ 7:46 ] Play Now | Play in Popup | Download (1056)
Link to this post!
Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
February 22, 2011 9 Comments

Create Dynamic Chart Titles in Excel

A viewer wrote to ask for my help in creating dynamic chart titles in Excel. He has created a series of pivot table reports and pivot charts. He wants the titles for his charts to update dynamically. That is, as he selects a new filter for his pivot table, he wants the title in his pivot chart to match the value label in his filter.

Dynamic Chart Titles
Dynamic Chart Titles

 

Note: 

This technique will work for all Excel Charts and Chart Types. It is not restricted to Pivot Tables.

The solution is rather simple: You link the Chart Title to a formula.

Link Chart Title to Formula

Select a cell that is outside the range of your Pivot Table – let’s say cell H1. An example formula is:

=”Sales for Fiscal Year “&D1

In this case, cell D1 contains the label for the value that you are selecting to filter your Pivot Table Report and Chart – e.g. 2008 as your Fiscal Year.

Notice that in the formula, I include the “TEXT” inside ” ” (double quotation marks. I also use the & (ampersand) to join the text to the cell reference (D1).

Finally, select the Chart Title and then, in the formula bar type =H1 (where H1 is the cell that contains the formula that we just wrote).

As a finishing touch, you can “hide” the formula in cell H1 by changing the FONT COLOR to match the background color for that cell.

It is that easy! Try it yourself after you watch this short (6 minutes) video lesson.

Invitation to Visit My New Online Shopping Site

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

My new, secure, online shopping website – http://shop.thecompanyrocks.com – is now open. I invite you to stop by for a visit and get more information about the many resources  that I offer for sale including, “Learn how to “Master Excel in Minutes – Not Months!”

Watch Tutorial in High Definition

Follow this link to my YouTube Channel – DannyRocksExcels – where you can watch this video lesson in High Definition, Full-screen mode.

Creating Dynamic Chart Titles in Excel [ 6:09 ] Play Now | Play in Popup | Download (2578)
Link to this post!
Filed Under: Excel Charts, Excel Formulas, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
February 20, 2011 Leave a Comment

Download My Chart of Popular Keyboard Shortcuts for Word

Word Keyboard Shortcuts

Word Keyboard Shortcuts

I am pleased to make available to you – at no charge – my new chart of “Popular Keyboard Shortcuts for Word.”

Click on the link to download my chart of Word Keyboard Shortcuts as a PDF file. CR – Chart of Word Keyboard Shortcuts

I have organized the keyboard shortcuts into these categories:

Keyboard Shortcuts for Word

  • Basic File Operations
  • Undo, Restore or Repeat Actions
  • Move the Cursor
  • Select Words, Lines and Paragraphs
  • Use F8 Key in Selection Mode
  • Cut, Copy and Paste Selection
  • Delete Text and Graphics
  • Apply Formatting to Characters and Words
  • Find, Replace and Go To Text, Formatting and Document Location
  • Format Paragraphs
  • Insert Fields and Special Characters
  • Navigate in Word Table
  • Work in Outline View
  • Apply Styles
  • Switch Document Views
  • Review Documents and Create References

I took care to select and organize , what I feel are, the most popular keyboard shortcuts to use when working in all versions of Microsoft Word. Many people prefer to work without the mouse – keeping their hands on the keyboard. Most people, use a combination of mouse clicks and keyboard shortcuts when writing, editing and formatting their Word documents. Regardless of your experience working with Word, I think that you will discover one or two keyboard shortcuts that you were not familiar with.

I am always delighted to hear from my readers. Do you have a favorite keyboard shortcut? If so, what is it? Please add your comments below to share your experiences with all of my readers.

My Chart of Popular Keyboard Shortcuts for Excel

Earlier this year, I published a chart of the most popular keyboard shortcuts for Excel. Follow this link to download this chart – at no charge – as a PDF file. CR – Updated Chart of Popular Excel Keyboard Shortcuts

Download My Free 50 Minute Video Lesson for Excel Keyboard Shortcuts

I am pleased to announce that – when you click this link – you can get information on how to download my free “extended length” video recording where I demonstrate how and when to use Keyboard Shortcuts in Excel.

Link to this post!
Filed Under: Keyboard Shortcuts, Word Tips Tagged: , , , , , , ,
February 17, 2011 2 Comments

How to Find and Change Numbers Stored as Text in Excel

Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”

Prevent GIGO

Prevent GIGO

This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.

Looking for Numbers Stored as Text

As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.” 

If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!

Use the ISTEXT() or ISNUMBER() Functions

An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.

Use Conditional Formatting to Highlight Cells with Number Stored as Text

I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.

Use Paste Special – Add

The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!

Learn how to “Master Excel in Minutes – Not Months!”

Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

How to Find and Change Numbers Stored as Text in Excel [ 7:19 ] Play Now | Play in Popup | Download (1308)
Link to this post!
Filed Under: Conditional Formatting, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
February 16, 2011 5 Comments

How to Find and Delete Duplicate Records in Excel 2007

It is easy  – much too easy – to allow duplicate records to creep into an Excel database. Fortunately, starting in Excel 2007, it is really easy to first find and then delete duplicate records.

Remove DUplicate Records

Remove Duplicate Records

With the price of postage climbing, you cannot afford to send duplicate copies of a mailing piece. Not to mention that doing so reflects poorly on your company’s organizational skills. Likewise, having duplicate account entries makes for extra work and confusion.

Use Conditional Formatting to Highlight Duplicate Records

Starting in Excel 2007, Conditional Formatting got a lot easier to use. One of the menu selections is “Show Duplicate Records.” Now, with just a few mouse clicks, you can format the records that show duplicate values in your data set. I like to use this technique prior to acutally duplicating records so that I can get a sense of the number of possible duplicate records in my data set.

Selecting Duplicate Records to Delete

As a best practice, I always make a backup copy of my data set before I actually delete records. This way, I can quickly recover from any accidental deletions of non-duplicate records. When you choose the command to “Remove Duplicates,” a dialog box opens to reveal each field in your data set. Generally, you want to select all of the fields to narrow down the list of records to delete. Since I have already previewed the possible duplicate records by using Conditional Formatting, I have a pretty good idea of how many records will be deleted. You do not see a preview of the exact records that will be deleted, so make sure that you have a backup copy of your original list in order to restore any “accidental” deletions.

These new commands and menus introduced in Excel 2007 make deleting duplicate records a very quick process. Use them – wisely – to save yourself time, confusion and money!

Learn how to “Master Excel in Minutes – Not Months”

Watch this video lesson in High Definition, Full Screen mode. Follow this link to view this on my YouTube Channel – DannyRocksExcels

Secure Shopping Cart

Secure Shopping Cart

This is one of the tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” CLick here to open a secure shopping cart to purchase it.

How to Find and Remove Duplicate Records in Excel 2007 [ 4:26 ] Play Now | Play in Popup | Download (1082)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Tips and Time Savers Tagged: , , , , ,
February 15, 2011 Leave a Comment

How to Convert from Military to Standard Time in Excel

Viewers and clients frequently ask me how to convert times displayed as military time (1700 hours) to standard time (5:00 PM). Many conpanies capture time using the 24 hour clock (military time) however, many end-users, particularly in the United States want to see time expressed as standard time with AM and PM designations. 

Time on Clock

Digital Time

Time Stored as Text Value or as a Number?

The first step in the conversion is to determine how the time value is “stored” in Excel. Is it stored as a “text value” or is it stored as a numeric value? This will determine how we convert military time to standard time. In this lesson, I demonstrate how to extract text characters, join text strings and then use the TIMEVALUE() Function in Excel to make this conversion. 

Use Text Functions and the “&” (ampersand)

Next, I use the LEFT() function to extract the two left-most characters from the text string (0100) and the RIGHT() function to extract the two right-most characters. In Excel, you use the “&” (ampersand) to join text strings. When you insert the “:” (colon) between the hours and the minutes, be sure to enclose it inside ” “(double quote marks). 

The TIMEVALUE() Function

In order to perform time elapsed calculations in Excel, you need to have time values “stored” as numeric values. You can use the “visual clue” – to which side of the cell is the time value aligned? Text values align to the left side of the cell; numeric values align to the right side of the cell. After you use the TIMEVALUE() function, be sure to format the numeric time values to meet your needs. 

If you import time values that are “stored” as numbers – they align to the right side of the cell – but are formatted as military time (13:30) all you have to do is change the fomratting to standard time (1:30 PM). 

Follow this link to watch this video lesson in High Definition, Full Screen mode on YouTube. My channel is DannyRocksExcels.

I invite you to subscribe to my video podcast, “Danny Rocks Tips and Timesavers,” available for free on iTunes. Click here to view my videos at the iTunes store.

Visit My New Online Shopping Website

I invite you to visit my new, secure shopping website – http://shop.thecompanyrocks.com – where you can view all of my products and “Learn how to “Master Excel in Minutes – Not Months!”

How to Convert from Military to Standard Time [ 6:40 ] Play Now | Play in Popup | Download (1134)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
February 7, 2011 Leave a Comment

Working With Array Formulas in Excel

There are at least two benefits to using an Array Formula in Excel:

  1. They will speed up creating your formulas by, in many cases, eliminating intermediate formulas.
  2. They will add a level of protection to your formulas because you cannot edit the individual cells in an Array Formula. You must edit “all” of the cells.
Array Formulas
Array Formulas

In this lesson, I first demonstrate how to select and use arrays with the SUMPRODUCT() Function in Excel. SUMPRODUCT is a very handy function to use when you want to first multiply (PRODUCT) the cells in two or more arrays and then total (SUM) the results.

 
Next, I demonstrate two ways to create Array Formulas. Notice that in the second example that it is important that you remember to select ALL of the cells that will contain the results of the Array Formula. It is vital that all arrays are of Equal Size (Same number of cells in the same dimension – horizontal and vertical).
 

Entering Array Formulas

Use the keyboard combination of Ctrl + Shift + Enter when entering an Array Formula. Do not press the Enter key alone – this will result in an Error for your formula! Some people refer to Array Formulas as CSE Formulas to remember the Ctrl + Shift + Enter combination. Notice, in the Formula Bar,  that Excel automatically adds { braces } around the Array Formula. Do not enter these manually.

Editing Array Formulas

You gain a level of protection for your formulas when you use Array Formulas because you cannot select a single formula cell to edit it. You must select all of the cells that contain the Array Formula before you can edit it – or delete it. If you don’t select all of the cells first, you receive an Error message in a pop-up window. Remember to press the “CSE keyboard combination” when you complete your edit of the Array Formula.

Learning to use Array Formulas is an essential skill to acquire if you want to master Excel. I guarantee that you will be able to write more accurate formulas when you use arrays.

Watch this Video on YouTube

Click this link to watch this Excel Video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Learn how to “Master Excel in Minutes – Not Months!”

Working with Array Formulas in Excel [ 7:45 ] Play Now | Play in Popup | Download (1004)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
February 2, 2011 Leave a Comment

How to Use Date Functions and Date Formatting in Excel

In this lesson, I respond to two recent requests from viewers for help in Using DATE Functions in Excel and also, how to apply Custom Date Formatting. I get similar requests on a frequent basis from both viewers and clients.

Solve Problem #1 

My first viewer wants help in using the DATE() Function in Excel. She wants to calculate a date that is 90 days after the starting date for a project. She encountered two problems

  1. Initially, the DATE() Function produced and error – because she forgot to ‘nest” the YEAR(), MONTH()+9, DAY() Functions inside the DATE() Function.
  2. She was not expecting a date to be filled in the formulas column when there was no starting date for the project – She needed to include an IF() function to perform a LOGICAL TEST – Does  the Starting Date = 0, Value if TURE, ” ” (to display nothing), Value if FALSE – to perform the calculation.
Excel Custom Date Formats

Excel Custom Date Formats

Solve Problem #2

My second viewer wanted my help to ensure that the date values that he “pasted” into a range of cells were in the (Custom) format of “yyyy-mm-dd” (Year, Month, Date).

In this case, we need to take two steps:

  1. Format the destination cells using the CUSTOM FORMAT of yyyy-mm-dd that we create.
  2. After “Pasting” the Copied Cells, use the “Paste Options” dialog box to select – “Match Destination Formatting.”

I also demonstrate how to use the NETWORKDAYS() Function and the WORKDAY() Function – with an optional list of “Holidays” to exclude. And, finally, I show you the “pitfalls” that occur when you import a “text” file of dates that use a variety of formats. In order to perform DATE calculations, a date must be stored as a NUMBER in Excel. It must be right-aligned in the cell.

You can watch this Excel Video Lesson in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcel – by clicking this link.

Learn to “Master Excel in Minutes – Not Months!”

How to Use Date Functions and Apply Date Formatting in Excel [ 12:06 ] Play Now | Play in Popup | Download (1075)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , ,