Argee's Office Help Blog

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.

This article, John R. Durant's WebLog : Why VBA Still Makes Sense , presents a solid case  for why VBA is the ‘next level’ when it comes to application development in Access and other members of the Office suite. John provides an excellent of VBA since its early days to the present and on into Office 2010 including his perspective on the ‘fit’ between .NET and VBA. John R. Durant's WebLog : Why VBA Still Makes Sense is definitely a worthwhile read.

If you are a Project user or are interested in the latest developments in Microsoft Project, check out this Office Team Blog overview. The new version looks exciting.

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?