Quote of the Day: Copy from one, it's plagiarism; copy from two, it's research. --Wilson Mizner

I tried a little experiment yesterday and was pleasantly surprised by the results. A while back I had learned how to link a chart title to data stored in an Excel worksheet. That in itself is an enhancement to charting.

If you have done any charting (graphing) in Excel, you will know that most of the chart’s values are stored in a worksheet. Change the worksheet values, and the chart will automatically update. Here’s an example. Let’s say that I am using a worksheet to prepare a monthly sales presentation to management. Management want to see the relative contribution of each salesperson to year to date sales. So I have set up a simple pie chart to display the information. Obviously, I have much more to do in my job than just to prepare this once a month presentation so I want to produce this chart as efficiently as possible.

Now, when I created the chart, using the chart wizard, I had to ‘hard code’ the title. That means that each month after I have updated the underlying date, I have to manually edit the chart title so that it reflect the reporting month. That takes a minute or two and for me it’s easy to forget. Forgetting to change the title costs more time. I have to re-open the file and make the change. Notice how the chart title at the moment is simply, “YTD Sales,” not reflecting the fiscal period.

To keep management of this chart as simple and efficient as possible I want to enter data only into the five cells that are highlighted in yellow. Of course the sales figure cells could actually be getting their values from formulas referring to the raw data. Then all I would have to do is enter the period ending date (and even that cell could contain a formula referring to the raw data.)

Here I have made one small change. The textbox containing the chart title no longer contains literal text. I have replaced the literal text with a formula that refers to a cell  containing the title exactly as I would like to see it in the chart. Now, regardless of the reporting date, the chart will always indicate the correct date.

These examples show an embedded chart. Having a date specific title is not as critical in embedded charts as it is in charts that are rendered on separate chart sheets in the workbook. In those cases, you can’t just glance at the data to see the reporting date  applicable to the chart without moving to the other worksheet.

The formulas revealed

The next graphic exposes the formulas involved. I have switched the worksheet to display formulas rather than the values generated by those formulas using ctrl+`. For this example, the sales results are simply random numbers because I don’t have any real data that can be published. The chart title text box contains a formula as well but it is still displaying the value rather than the actual formula which, in this case, is =$D$4.

Cell D4 itself contains a formula that creates and formats the text I would like as the chart title.

Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title. Besides, I just think this is a cool possibility.

Remember One Trick

The is just one small thing to remember when you want to try this yourself. Ordinarily, when you want to enter a formula you select a cell and then type the formula in either the cell or the formula bar.  When you want to enter a formula in a chart title text box, select the textbox but do not type anything in it; when you have the textbox selected, type the formula in the formula bar.

Quote of The Day Ask not what the world needs. Ask what makes you come alive... then go do it. Because what the world needs is people who have come alive. Howard Thurman

One day I was teaching an Introduction to Excel class when one of the participants questioned why it was important to ‘do everything quickly.’ I had discussing ways to efficiently create a workbook. I took the comment as coming from the perspective that many North Americans and Canadians, specifically, live life at too fast a pace. I happen to agree with that point of view but at the same time I prefer to get the drudgery of a task quickly out of the way so that I can dedicate more time to parts of the task that I find more interesting.

Autofill is an Excel feature that many self-taught users are not aware of. It’s easy to miss the tiny clue that the feature even exists. Autofill has two main purposes in Excel, extending a series, and copying formulas.

The Autofill handle

Unless you look very carefully, it’s easy to miss the autofill handle at the bottom right corner of the selection outline.

Once you have found the handle, you also need to watch the shape of your mouse pointer carefully:

When the mouse pointer resembles a black plus-sign, you are pointing at the autofill handle. You are ready to autofill. If the mouse pointer is any other shape, you are not  ready to autofill:

To perform the autofill, point your mouse at the autofill handle, then press and hold  the left mouse button. Then move your hand in the direction that you want to fill, either vertically or horizontally.

Extending Series

Excel recognizes certain data as belonging to one or other series. Weekday names, month names, expressions like ‘Qtr 1’ or ‘1st Quarter’, these are all series that autofill can automatically extend for you. Here is a brief video demonstration in which I create a series of month names for an income and expense worksheet. (I recorded

this demo using Excel 2007 so there are some screen features that you may not see if you are using a version of Excel earlier than 2007. The basic autofill techniques and results are the same however. In the demo, I started with the abbreviation for the month of January. If I had entered the full name, then autofill would have inserted the un-abbreviated name for each month into the cell. Note also that the case of the starting cell determines the case of all of the autofilled cells.

Copying Formulas

If the only thing autofill could do was to extend series, saving us from some typing drudgery, it would still be a useful tool that every Excel user should be aware of.

However, autofill can do more. Try autofilling a formula, instead of the first cell of a series. You will find that  autofilling may faster than copying and pasting formulas when you want to apply a formula across several rows or columns

Advanced Autofill Techniques

Remember that Autofill can extend any series as far as you need.  Now, here are some specialized techniques.

• create a series of sequential numbers
  • in Excel 2000, enter the beginning number in a cell and then hold the <Ctrl> key down while you drag the autofill handle.
  • in Excel XP (2002) and later, enter the beginning number in a cell, drag the autofill handle, then click the fill options button and select series.
  • if you want a sequential series to the right or left of a column of data, enter the starting value in the cell to the left or right of the column containing the data, point to the fill handle and double click.
• create an intermittent series
  • to create a series of even numbers, enter the first number of the series into one cell, the next number in the series in the next cell down or to the right (depending on the direction in which you want to fill), select both cells and then fill.
  • to create a series of five weekdays with no weekend days, create a series from the first day to the last day you want to include in the week, copy that last of days and paste it in the immediate next cell, then select all of the cells that have a day name in them and fill for as many weeks as you need.

This video demonstrates these techniques.

Perhaps not as sensually satisfying as raindrops on roses or whiskers on kittens, but when it comes to Excel, these are a few of my favourite things:

• autofill

Quickly copy formulas or create a series of months, days, quarters, or other custom series.

• cell and range names

Use cell or range names to make formulas easier to understand at a glance.

• templates

Stop re-inventing the wheel when you need new workbooks to handle data with tried and true formulas and layouts

• lookup functions

Reduce the amount of repetitive data in your workbook

• absolute references

Make it easier (and faster) to copy formulas that need to refer to the same value regardless of where you copy them to

• 3-D formulas

Create a worksheet to summarize data from detail worksheets using simple formulas.

• header and footer improvements (Excel 2007)

Custom headers and footers made a giant step forward with Excel 2007

• tables (Excel 2007)

Although the table concept is not new with Excel 2007, this version has added some amazing enhancements to tables.

• keyboard shortcuts

Keyboard shortcuts were absolutely essential in Dos days. They may not be essential in today’s mouse-based graphical user interface but they are as useful as ever.

• date and time
• copy cell data
• paste
• cut
• undo
• display formulas or their results
• draw borders (ctrl-Shift-7)

 

• custom sort order

For values that you want to put in order in a non-standard way

• custom number formats

For those numbers that are not numbers (you know – values like telephone or serial numbers that you would never ‘do’ math on.)

Watch for these and other articles in the coming weeks. If you have other favourite Excel things, drop me a note and tell me what they are and why you like them?

Data validation lists help restrict data entry to values that you have included in a list of valid/acceptable values. For example, you can use a date validation list to prevent 'creative' spelling differences for expense categories. The feature adds a 'drop-down' to the cells to which you have applied data validation.

I have long had a problem with one aspect of the feature however because of this error message:

One standard I try to maintain in my workbooks and encourage my trainees to use is that each data table should be on its own worksheet. According to the error message, however, you must store a data validation list on the same worksheet as the data table for which you want have some cells whose values come from a lookup list. Keeping the lookup list on a separate worksheet would keep its details 'out of site, out of mind,' only to be seen when the user is accessing one of the cells that has data validation applied. The error message implies that I cannot enforce my design standard if I want to use a data validation list.

Thanks to Monique, a trainee with whom I was working last week, I learned that the error message does not tell the whole truth. It is indeed possible to have a lookup list on a different worksheet. This means that I can manage my data in the way I want to manage it; I can have the lookup list for a data table on a separate worksheet. It also means that I can more easily maintain lookup lists by keeping them all on a single worksheet dedicated to lookup lists.

How you go about it depends on the Excel version you are working with. For Excel 2000, XP (2002), and 2003, you will need to create a range name for each lookup list. In Excel 2007 you can use range names or type the lookup reference in the Sheet Name!Range format.

Try it out. In a new blank workbook, name one of the sheets Lookups, and another, Data. On the Lookups sheet type a list of country names. In this example, 'Canada' is in C1, and 'USA' is in C2. Obviously, you might have a much longer list of country names. Name the range 'Countries'. Be sure to include the full list of countries when you are naming the range.

Now switch to the Data worksheet. Select the column (or cells) to which you want to apply the Data Validation List. In Excel 2000 - 2003, Select Validation from the Data menu. In Excel 2007 you will find Data Validation on the Data tab of the ribbon. In all versions, this will open the Data Validation dialogue.

Use the Data Validation dialogue Settings tab to Allow List. In the Source text box enter "=Country" (without the quotation marks.)

Although I don't recommend it, in Excel 2007, you can refer to the list using Sheet Cell reference by typing the reference into the Source text box ("=Sheet!Range" format.) If the sheet name includes a space you will need to enclose it in single quotes. Because the dialogue box is open you will not be able to select the sheet and range using your mouse. For that reason alone, I prefer to use a range name for the data list. Use F3 to be able to select the relevant range name.

"Where can I find a complete list of Excel keyboard shortcut key?' This question comes up in my classes from time to time. You can find a full list on the Office On-Line web site. You may find the list just a little overwhelming, given that one or more shortcuts are associated with most or your keyboard keys. So, here a few of what I consider to be the more useful shortcut combinations.

 

Shortcut	Description	Comment
CTRL + ;	Enter the current date	unchanging date stamp
CTRL +  :	Enter the current time	unchanging time stamp
CTRL + `	Toggle display of cell values and formulas on the worksheet	this is the key on the left side of top row of standard keys (beside the '1' key
CTRL + '	Copies the formula from the cell above
CTRL  + SHIFT + "	Copies the value from the cell above
CTRL + SHIFT + *	Selects cells around the active cell bounded by blank rows and columns	this is the shifted 8 key in the top row of standard keys
CTRL + HOME	Moves to the beginning of the worksheet	Usually moves to cell A1 unless Freeze Panes is in effect then CTRL + HOME will move to the Top Left Cell of the Bottom Right Quadrant
CTRL + SHIFT + HOME	Extends the selection from the current cell to the beginning of the worksheet	If Freeze Panes is in effect, extends the selection from the current cell to the Top Left Cell of the Bottom Right Quadrant (if the Active Cell is in the Bottom Right Quadrant). If the Active Cell is not in the Bottom Right Quadrant, extends the selection to include the Top Left Cell of the Bottom Right Quadrant.
CTRL + 9	Hide the selected rows
CTRL + 0	Hide the selected columns
CTRL + SHIFT + (	Unhide rows within the selection
CTRL + SHIFT + )	Unhide columns within the selection
CTRL + C	Copy	Repeat CTRL + C to open the Office Clipboard
CTRL + X	Cut
CTRL + V	Paste the last item cut or copied	If the marquee is active
CTRL + Z	Delete the last entry you typed or reverse the last command you executed
CTRL + Y	Repeat the last command or action if possible	(Re-does the last undo)
F3	Opens the Insert Name dialogue	useful to ensure you don't misspell user defined names in formulas
F4	Creates absolute reference	while entering a formula

 

This is just a small selection of the keyboard shortcuts available in Excel. Check the Office Online link at the beginning of this article for more that may be useful to you.

Thanks to Clayton Barranger-Mitchell, who recently attended one of my Excel classes, for this solution.

Scenarios are a particularly useful Excel feature when you are budgeting or forecasting and calculating results based on certain assumptions. The feature makes it easy to experiment with various what-if options in the data and yet not lose track of the various sets of values when you want to try out new variations.

Here is a simple example:

Original estimates and assumptions:

Variable Cost Rate	30% of Gross Sales
Gross Sales	75,000.00
Variable Costs	22,500.00
Fixed Costs	50,000.00
Estimated Profit	2,500.00

My one complaint with the feature has been that the manager and reports both display absolute references to the relevant cells rather than what one might refer to as 'meaningful' names. I have always found it easier to think of data in terms of what it is rather than where it is located. So I might refer to 'salaries' rather than $C$36, for example.

Here's what the Scenario Values dialogue looks like if you use only cell references for the changing cells of the scenario:

The Scenario Values dialogue labels its entry boxes with standard absolute cell names. The corresponding Summary Report has similar reference and would have to be edited before you publish or present it at a meeting.

Fortunately, the problem has a simple solution. Simply create a name for each of the cells to which the scenario refers.

With meaningful names it is easier to work with the Scenario Values dialogue and the Summary Report is more readable without your having to modify the cell names in the report. Underscores in the names are a byproduct of Excel's requirement that range names may not have spaces.

Thanks to Héctor Miguel Orozco Díaz for the newsgroup posting where I first learned of this technique.

You may find it necessary at some time to prevent  the insertion of one or more columns in a worksheet. For example, consider this table:

For the sake of the demonstration, let's assume that you do not want columns to be inserted between columns C and D.

• First identify two cells, one in each of column C and D, that you do not want to use for any other purpose. In this example I will use cells C1 and D1.
• Select both cells and type the formula

=""

• after typing the formula press

Shift-Ctrl-Enter

(press and hold the shift and ctrl keys and press Enter.)

If you inspect the two cells now you will see that they each contain a formula that looks like this {=""}. This is known as an array formula. Having an array formula will prevent operation, such as column insertion, that would affect the layout of the array.

This method is not fool proof but it does provide at least some protection against inappropriate column insertion. Combining this technique with hiding the row containing the array formulas and worksheet protection adds a little bit of security to prevent the accidental deletion of the array formula. If you do not want to hide the relevant row or your version of Excel does not allow you to prevent users from selecting locked cells, enable worksheet protection format the cells' protection as locked and hidden before you enable worksheet protection.

A video demonstration of this technique is available here.