Excel Zen Spreadsheet Blog

Help Menu Commands in Excel 2007

In this post we look at the Help menu commands in Excel 2007. This also concludes our menu series. We have barely scratched the surface on commands in Excel 2007. Not convinced. Open up the Excel Options and under Customize you will see literally tens if not hundreds of commands that can be added to the Quick Access Toolbar. Thankfully, the average user only ever needs a fraction of these commands.

I’ve also included additional resources below if you want to follow up on these series.

1. A downloadable list of Excel 2007 commands covered in this series in pdf format. You can search for the commands locally.
2. Download an interactive flash command reference guide. This is a self-extracting executable. It is ideal for those who prefer a visual approach to learning.
3. Download a spreadsheet from Microsoft. The spreadsheet has both the toolbar and menu commands. Also check out this page which is an easy to follow primer on the Excel 2007 interface.
4. A search commands add-in from Microsoft Office Labs. After installing the add-in, you can type commands to search for. I’ve had limited success with this add-in. For one, it sometimes returns to many results and for another, I wasn’t able to search for commands that start with a number eg. 3-D.

Help Menu

Window Menu Commands in Excel 2007

We are at the second last post of the menu series. This post covers the Window menu commands in Excel 2007.

This menu is a simple and straightforward change from Excel 2003 to Excel 2007. The commands are located under the View tab in the Window’s group.

Window Menu

Tags: commands, Excel 2003, Excel 2007, Menu, window

Excel Automatic Filling (AutoFill) – Data and Patterns

In this post we continue exploring Excel’s autofill feature. Last time we looked at the basics of filling. We saw how to enter data in a few cells and then use the fill handle to extend that data to a larger range of cells. This time we take a closer look at the type of data Excel can recognize and fill.

Excel can recognize and autofill:

• Dates
• Times
• Numbers

The table below shows some examples of how Excel fills cells. Data has been entered in a single cell and then extended to other cells by using the autofill feature:

There are a couple of things to note here. Firstly, the left mouse button was depressed while the fill handle was dragged. It is possible to use the right mouse button instead and if you do so Excel gives you more options when filling cells. We will take a look at using the right mouse button in the next post. But for now we’ll keep it simple by considering what happens when you use the left mouse button.

The second point to note is that Excel has in some cases repeated the data that was entered and in other cases filled cells with a sequence of values. Numbers have been filled by repeating values whereas times and dates have been filled by creating sequences. If you want more control over the way in which Excel fills cells, you can enter data in two cells instead of just one. Basically you are giving Excel enough information to recgnize the pattern you want to use when filling cells. To see this, let’s take a look at what happens when data is entered in two cells and then filled using Excel’s autofill feature:

Here you can see that Excel has taken the difference between the two initial cells and added that difference to successive values to create a sequence of values. For example, the difference between 5 and 2 is 3. This has then been used as a “step value” to generate a sequence of values: 5+3=8, 8+3=11, 11+3=14 etc. Excel uses the same approach for dates and times. Take for example the two times 11:00 a.m. and 11:45 a.m. There is a difference of 45 minutes between these two times. If we add 45 minutes to 11:45 a.m. we get 12:30 p.m., and adding a further 45 minutes gives us 1:15 p.m. – this is exactly the sequence that Excel has produced.

There is one more thing that we need to talk about. Did you notice that Excel was able to increment numbers inside text? Take a look at the bottom three rows in the table above and you’ll see what I mean. This can be really handy if you need to label your spreadsheet data – adding text labels like “Observation 1”, “Observation 2”, “Observation 3”, … becomes effortless if you use Excel’s autofill feature.

Finally, let’s take a look at what happens when we enter values in three cells and then use autofill to fill a range of cells. Remember that we are dragging the fill handle while holding down the left mouse button. Here are the results of some test data sets:

The results may look a bit random but they aren’t. Excel uses the TREND worksheet function to calculate the best linear fit to the data (in the sense of least squares) and then uses the fitted line to fill the selected range. The chart below illustrates this for the data in the first row of the table above:

The examples that we’ve looked at are fairly limited. Autofill actually offers more flexibility and control than we’ve seen here. That’s something that we’ll explore in the next post.

Tags: Excel autofill, Excel automatic fill, fill dates, fill handle, fill series

Excel Automatic Filling (Autofill) - The Basics

Excel is full of time saving features. One such feature is the ability to automatically fill cells with data. Excel is able to recognize data and patterns and fill cells in an intelligent way. This is a real boon when it comes to data entry as it can save you a lot of time.

Let’s look at the basic steps in automatic filling:

1. Enter data in one or two cells
2. Select the cells containing the data you’ve entered
3. Drag the fill handle down a column or across a row
4. Release the fill handle

Basically you need to enter sufficient for Excel to recognize what you want to fill and then tell Excel what range you want filled. Let’s make things a bit clearer by looking at an example. Suppose you want to fill a range with the months of the year. This is easy. Just enter “January” into a cell:

Select the cell containing January:

Move the cursor over the fill handle. (Note how the cursor changes to a plus sign “+” when the mouse is positioned over the fill handle.) Click and hold down the left mouse button:

Then drag the fill handle down:

Finally release the fill handle:

As you can see, Excel has filled cells with the months February through to December. In this example we clicked the left mouse button and dragged the fill handle. Excel will also let you click the right mouse button instead of the left mouse button. If you use the right mouse button then Excel will display a pop up menu that gives you more control over the way in which cells are filled. We’ll ignore this option for now for the sake of simplicity and discuss it in detail in a later post.

Excel can recognize patterns and replicate them when filling cells automatically. An example will help you see this. Let’s fill some cells with a series of months starting at March 2000 where the interval between successive elements in the series is three months. We can do this by entering “March 2000” in one cell and by entering “June 2000” directly below:

Select the cells containing “March 2000” and “June 2000”:

Move the cursor over the fill handle. Click and hold down the left mouse button:

Then drag the fill handle down:

Finally release the fill handle:

So far we’ve filled cells down columns. You can also fill cells across rows by dragging the fill handle horizontally. In this case you’ll need to make sure that you enter your initial data in the same row that you want to fill. So if you want to enter the series 5, 10, 15, 20, … in a row, you’ll need to enter 5 and 10 next to each other in the same row before you autofill. These pictures illustrate this:

Autofill is a really useful tool. But to make full use of it we need to understand the type of data and range of patterns that Excel can recognize and fill. That’s something we’ll look at in the next post.

Tags: Excel autofill, Excel automatic fill, fill dates, fill handle, fill series

Chart Menu Commands in Excel 2007

Part 8 of the menu series covers the Chart menu commands in Excel 2007. The Chart Tools tab is activated only when a chart is selected. The Design, Layout and Format tabs reside underneath the Chart Tools. In previous versions of Excel, creating and editing charts seems like a black art to some. My impression with the Chart Tools interface is that this is a more approachable method for a lay person to create and edit charts.

Chart Menu

Tags: chart, commands, Excel 2003, Excel 2007, Menu

Data Menu Commands in Excel 2007

In Part 7 of the menu series, we look at the Data menu commands in Excel 2007. The Data menu contains quite a number of sub-menus which expand into commands galore in the table below.

If a list command doesn’t appear to work, make sure that the table is selected and then try again.

You will have to activate the Developer’s tab to access the XML commands in Excel 2007.

Data Menu

Tags: commands, data, Excel 2003, Excel 2007, Menu

Hey! Who Took My Tools?

My work tools seem to have a habit of walking away just when I need them. No matter how hard I try to organize them, they just scoot off.

In Excel 2007, you might be surprised to find that there isn’t a tab similar to the Tools menu. Your favorite add-ins haven’t disappeared. Instead they have been relocated to a new Add-Ins tab. The auditing, macro and protection tools have migrated to various tabs on the ribbon. Part 6 of the menu series covers the Tools menu commands in Excel 2007.

The auditing, macro and worksheet protection tools have moved to the Formula tab, Developer tab and Review tab respectively. Look for Goal Seek and Scenarios in the Data tab. The Add-ins Manager is now buried deep inside the Excel Options.

You won’t find the Developer tab unless you’ve enabled it. It isn’t enabled by default. To enable the Developer tab, open the Excel Options and navigate to the Popular category. Click on the check box that says Show Developer tab in the Ribbon and then click the OK button to exit. The Developer tab should now appear in the ribbon.

Tools Menu

Tags: commands, Excel 2003, Excel 2007, Menu, tools

Excel Formula: Summing with Multiple Criteria Part 2

In Part 1 we looked at the following array formula for summing cells in the range A1:A1000 which are less than 50 or greater than 100

=SUM(IF((A1:A1000>100)+(A1:A1000<50),A1:A1000,0))

This array formula is quite specific so we need to look for a more general array formula that acts like SUMIF but which accepts more than one criterion. What we want to come up with is a generalized array formula like the one below:

=SUM(IF(criteria, range_to_sum, 0))

At this point it’s a good idea to remember that we’re dealing with an array formula. Array formulas need to be entered by pressing the SHIFT, CTRL and ENTER keys together.

We want to be able to link criteria together using logical operators such as “and” and “or”. We do this by using addition whenever we want to do a logical “or” operation and by using multiplication whenever we want to do a logical “and” operation. This might seem strange because Excel provides the logical functions AND and OR which ought to do the job. Unfortunately both of these worksheet functions return a single value rather than an array of values when they are used inside an array formula.

So looking at the generalized array formula above we can re-write criteria as

c₁ op₁ c₂ op₂ c₃ op₃ ….

where

c_x is one of the criteria we are using to determine whether or not a cell is included in the sum (x = 1, 2, 3 …)

and

op_x is either + or * depending on whether we want an “or” operator or an “and” operator respectively (x = 1, 2, 3 …)

range_to_sum is the range over which the sum is taken.

You can use brackets to control the order in which the criteria are evaluated if you wish.

This looks rather complicated but a few examples will make things clearer:

Example 1

=SUM(IF(((A1:A1000>100)+(A1:A1000<50))*(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are either less than 50 or greater than 100 and which are also a multiple of 5. To see how this works let’s break down the first parameter in the IF function:

The MOD function divides by 5 and calculates the remainder. If the remainder is 0, the value is a multiple of 5.

So this array formula checks each cell in the range A1:A1000 to see whether it is greater than 100 or less than 50 and if so, it checks whether it is a multiple of 5. If a value satisfies these criteria, the IF function returns that value and otherwise it returns 0. The SUM function adds all values returned by the IF to get the final result.

Example 2

=SUM(IF((A1:A1000>100)+(A1:A1000<50)+(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are either less than 50 or greater than 100 or a multiple of 5.

Example 3

=SUM(IF((A1:A1000<100)*(A1:A1000>50)*(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are greater than 50 and less than 100 and a multiple of 5.

These examples apply criteria to a given range and then sum that same range. There is no reason why we can’t apply criteria to one range and then sum a different range. There is no problem using different ranges so long as the ranges have the same number of rows. Let’s look at an example. Suppose we want to sum those cells in the range A1:A1000 where cells in the same row satisfy these two conditions:

• the value of the cell in column B is greater than zero
• the text in the column C cell is “February”

We can find the answer easily by using an array formula like this:

=SUM(IF((B1:B1000>0)*(C1:C1000=”February”),A1:A1000,0))

Finally, some of you may be wondering why I haven’t mentioned the SUMIFS worksheet function. There are two main reasons why I haven’t talked about this function. The first reason is that the SUMIFS function is only available if you’re using Excel 2007. If you’re using an earlier version of Excel then this function isn’t available. The second reason is that the SUMIFS function only adds together cells when all criteria are met. You cannot, for example, use SUMIFS directly to calculate the sum of all values that satisfy two out of three criteria. In this regard using SUMIFS is not as flexible as the array formula approach.

Tags: array formula, conditional sum, excel formula, excel function, SUMIF worksheet function, SUMIFS worksheet function

Format Menu Commands Move to the Home tab in Excel 2007

In part 5 of the menu series, we cover the Format menu equivalent commands in Excel 2007. The bulk of these commands are now located under the Format command in the Cells group in the Home tab.

Format Menu

Tags: commands, Excel 2003, Excel 2007, Format, Menu

Sort by Cell Color

Most of you probably already know how to sort data by alphabetical or numerical order from highest to lowest or lowest to highest and by several columns if required. In Excel 2007, you can also sort by cell color or font color. In earlier versions of Excel, you can only achieve this using visual basic macros. This lesson covers sorting by cell color in Excel 2007. The steps are easily applied to sorting by font color by changing the a single sorting criteria.

Why sort by color?

Here are a couple of situations where sorting by color is useful.

Let’s say you’ve got a spreadsheet with conditional formatting and when the condition is true the cell is colored. You now want a report of all data that only meets this condition. No problem. Just sort by color and grab the data.

Another situation. UltraSleuth Gold Worksheet Compare produces a report of changed rows by coloring the rows. Want to just see the changes? No problem. Sort by color and grab the colored rows.

How to sort by color

Open spreadsheet containing data and colored cells to be sorted. Click on the data that you want to sort. Make sure you’ve clicked on at least a cell in the data that you want to sort or Excel will complain.

In the example, I’ve a spreadsheet with sales by month. I’ve set up a conditional format to highlight any sales for the month that exceeds the median of $4,425 as shown below.

From the Home tab find the Editing group and then click on the Sort & Filter button. From the drop-down menu select Custom Sort.

The Sort dialog box will open. Select Sales from the Sort by drop-down box, Cell Color from the Sort On drop-down box and select a cell color (in this case there is just one color) from the Order drop-down box. Click the OK button to sort.

The data should have been sorted with the colored cells moving up to the top rows as can be seen in the screenshot below.

To sort by font color, just select Font Color from the Sort On drop-down box when making your selection choices.

That’s it. Have fun.

Tags: color, Excel 2007, sort

Excel Formula: Summing with Multiple Criteria Part 1

Excel provides a useful way of summing cells that satisfy a given criterion. The SUMIF worksheet function is designed specifically for this purpose.

By way of example, let’s suppose you want to sum the cells in the range A1:A1000 which have a value greater than 9.95. You can do this easily by using the formula

=SUMIF(A1:A1000,”>9.95”)

The parameters used by the SUMIF function are fairly self-explanatory. The first parameter is the range to be summed and the second is the criterion that determines whether or not a cell is included in the sum.

One failing of the SUMIF function is that it can’t cope with multiple criteria. For example, you might want to sum cells which are between two given values. Unfortunately SUMIF can’t do this for you. In this series of posts we’ll look at a way of working around this limitation.

The approach we are going to take is to use an array formula which combines the SUM worksheet function and the IF worksheet function. An array formula performs multiple calculations and returns either a single value or multiple values. You can distinguish an array formula from an ordinary formula by looking at Excel’s formula bar – an array formula is enclosed by braces (curly brackets) like this {}. To enter an array formula you type in the formula as normal but instead of pressing the ENTER key you press the ENTER, SHIFT and CTRL keys simultaneously.

The screenshot above shows the array formula

=SUM(IF((A1:A1000>100)+(A1:A1000<50),A1:A1000,0))

This array formula calculates the sum of all cells in range A1:A1000 which are greater than 100 or less than 50. The IF function is calculated repeatedly – one time for each cell in the range A1:A1000. If a cell in this range is greater than 100 or less than 50 then the IF function returns the cell value and otherwise it returns 0. These values are then passed to the SUM function which calculates the desired result.

The following screenshot shows the array formula in action:

You can see how the IF function returns an array that is a subset of the range A1:A1000, where cells that are between 50 and 100 (inclusive) have been replaced with 0.

It is important to remember that we are working with an array formula. If you enter the formula by pressing the ENTER key the formula will most probably give you the wrong answer. You need to enter the formula by pressing the ENTER, SHIFT and CTRL keys together.

The formula we’ve looked at here is rather specific. It needs to be generalized so that you can adapt it to meet your own needs. In part 2 we’ll look at extending the formula to cover a more general class of criteria.

Tags: array formula, conditional sum, excel formula, excel function, SUMIF worksheet function

Insert Menu Commands Distributed over 5 Tabs in Excel 2007

Okay, so far you’ve studied the corresponding commands in Excel 2007 for the File menu, Edit menu and View menu. And you think … hmm … I get this new user interface. After all, most of the commands in each menu were ported into one area. For example the commands in the File menu are found mostly in the Office button. Likewise with the Edit menu. Most of the commands are in the Home tab. And ditto with the View menu commands. You therefore expect to find most of the Insert menu commands in the Insert tab. Well … yes and no. Of the 24 commands in the Insert menu, 4 are now found in the Home tab, 10 in the Insert tab, 6 in the Formula tab, 2 in the Review tab, one in the Page Layout tab and 1 didn’t make it.

What is clear is that users who are already familiar with the menu and toolbars will have to relearn the user interface.

Insert Menu

Tags: commands, Excel 2003, Excel 2007, insert, Menu

Excel Formula: Calculate Last Sunday and First Sunday in Month

It’s daylight saving time again. Clocks go forward on the last Sunday in September and get put back on the first Sunday in March.

Perhaps you, like me, need an Excel formula that calculates the first and last Sunday in a month. In this post I’ll discuss a relatively simple formula that does that for you. The formula can be extended quite easily to cover other days of the week too.

I’ll start with a formula for calculating the date of the first Sunday in a month. The formula I’ve come up with relies on three Excel functions: Date, Weekday and Mod. The basic approach is to use the Date function to find the first day of the month. By applying the Weekday function to this date we can work out whether the first day of the month is a Sunday, Monday, Tuesday etc. Armed with this information we can calculate how many days we need to add to the first day of the month in order to arrive at the date of the first Sunday in the month.

In this post I’ll assume that the month (1 for January, 2 for February etc) is entered in cell A1 and the year (2015 for example) is entered in cell A2. Based on this, the Excel formula for calculating the first Sunday is:

= DATE(A2, A1, 1) + MOD(1 - WEEKDAY(DATE(A2, A1, 1)), 7)

Here’s a screenshot of the formula in action:

The screenshot shows the formula in an expanded format along with the intermediate results of the calculation. In this case we’re calculating the date of the first Sunday in September 2015.

But what if you want to find the first Monday of the month? That’s easy. Just change the 1 that follows “MOD(“ to 2. This gives us:

= DATE(A2, A1, 1) + MOD(2 - WEEKDAY(DATE(A2, A1, 1)), 7)

With a minor adjustment the formula can be used for the other days of the week too. Just replace the “1” with 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday and 7 for Saturday.

Now that we’ve looked at calculating the first Sunday in a month, let’s turn our attention to the last Sunday in a month. We need only make a couple of small changes to the first formula to get a workable formula. Here’s the formula I’ve come up with:

= DATE(A2, A1 + 1, 0) - MOD(WEEKDAY(DATE(A2, A1 + 1, 0)) - 1, 7)

I’ve rearranged a couple of items in the first formula, used 0 instead of 1 in the Excel Date function and increased the month by 1. The Date function calculates the day before the 1st day of month A1+1 – that is, the last day of month A1. Given this we can calculate how many days we need to subtract in order to arrive at the last Sunday of the month. To make things clearer, here’s how you would use the formula to find the last Sunday in September 2010:

= DATE(2010, 9 + 1, 0) - MOD(WEEKDAY(DATE(2010, 9 + 1, 0)) - 1, 7)

It’s easy to amend this formula if you want to find the last day of the month for other days of the week. Just replace the 1 which precedes “, 7)” with 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday and 7 for Saturday. So, for example, the last Thursday in a month would be:

= DATE(A2, A1 + 1, 0) - MOD(WEEKDAY(DATE(A2, A1 + 1, 0)) - 5, 7)

These formulas can be extended still further. As an example, suppose you want to find the second Sunday in a month. This is easy to do - just add 7 to the result returned by the formula at the top of this page. If you want the third Sunday in a month, just add 14 instead of 7. Similarly, given the last Sunday in a month, subtract 7 and you’ll get the second last Sunday in a month.

For convenience I’ve summarized most of these formulas in the tables below:

First Day Formula

Last Day Formula

Tags: date function, excel formula, excel function, weekday function

How to Find Hard Coded Values in Formulas

A hard coded value in a formula is a formula that contains one or more numbers entered as data into the formula. Some examples of these are as follows:

= A1 + 150
= 5 + 6
= 3 + A3 - I3 * K3 + 9 / J3
= Sin (40)
= 600 * tax_rate
= sum (A6, 30, 50)

In the examples the data are numbers entered as constants.

Hard coding of values in Excel can cause problems

A common issue with entering data into formulas is that although the data may be valid now it may not be valid in the future. For example if a tax rate of 33% is hard coded, the tax rate may change in future. Even with a moderate size spreadsheet, it is possible to miss cells where the tax rate was hard coded.

Another common issue is that it may be perfectly clear why a number is hard coded into a formula when it is done. But 6 months down the track, this may be as clear as mud. How many times have you come across a formula like this: =10510+2020+1950? What makes perfect sense at the time won’t mean much 6 months on.

Changes in spreadsheet ownership only exacerbates these problems.

By now, I hope that you are convinced that checking for hard coded values in formulas is a good idea.

How to find hard coded values in formulas

I’ve utilized UltraSleuth Gold’s Advanced Search feature to look for hard coded values in formulas. You can download a 30-day trial version of our Excel add-in from our website. After downloading the add-in, install it and enter the evaluation serial number to complete the installation.

Done that? Are we set to go?

Open up Excel. Enter the above formulas into a worksheet. For example in cell B1 type in =A1+150. Do the same for the rest of the formulas in cell B2 to B6.

Next open UltraSleuth Advanced Search. Enter the information into the dialog box as shown in the screenshot below and click the OK button to run the search.

Click on the image if you want to see a larger screenshot.

We are using the regular expression engine to locate numbers in formulas that match a certain pattern. The pattern that we are trying to match is (=|\+|-|\*|/|\(|,|\s)[0-9]+. The second part [0-9]+ means match one or more digits between 0 to 9. The + means one or more. Examples of matches are 5, 10, and 224. The first part means the number must be preceded by either an ‘=’ or ‘+’ or ‘-’ or ‘*’ or ‘/’ or ‘(’ or ‘,’ or ‘\s’. The or is denoted by a | symbol in a regular expression. The + and * symbols are special functions in a regular expression. To refer to these literally, the symbols have to be escaped with a backslash \. The \s means any whitespace character. We are therefore matching the first occurrence of a number that is preceded by an =, +, -, *, /, (, comma or whitespace. If there is a second set of numbers in the formula, it won’t be matched. This is reasonable since we only need to flag the first instance of a number. The more complex the regular expression pattern the greater the processing time.

Here is the link to Advanced Search’s regular expression syntax if you want to read more about it.

Advance Search will produce a list of results that matches that search term. It should look something like the screenshot below. You can double-click within the cell in column C to go to the cell referenced in the spreadsheet that you’ve searched. Make sure that the spreadsheet is opened or this won’t work.

Searching for hard coded values inside Excel Names

Well since you’ve read this far, here’s a bonus.

If you want to find hard coded values inside Excel names, make sure that you’ve set the scope of the search to the Active Workbook and check Names check box in the Look In section.

In this example, I’ve defined global names for discount=40% and margin=1-discount and these are returned in the first two results of the search.

That’s all there is to it. Let us know how this method works out for you.

Happy spreadsheeting!

Tags: Advanced Search, excel formulas, hard coded values, regex, UltraSleuth Gold

Office 2003 SP2 Support Ending in October 2008

Still using Office 2003 SP2? Well, the bad news is that Office 2003 Service Pack 2 support ends on 14 October 2008 as announced by Microsoft. In order to continue support in Office 2003 you will need to upgrade to the service pack 3 (SP3).

Unfortunately, SP3 blocks a number of file types. You can enable this again by following the instructions in kb938810. Alternatively, convert your documents to OpenXML using the Office Compatibility Pack. Viral Tarpara’s blog has instructions on how to do this.

Once you’ve upgraded, you should continue to receive updates until 2014.

Tags: Excel 2003, Office 2003, SP2, SP3, support

View Menu not Quite the View tab in Excel 2007

In part 3 of the menu series, we look at the View menu equivalent commands in Excel 2007. Part 1 covers the File menu commands and part 2 the Edit menu commands. Refer to the previous articles for the convention used in the table below.

For the statistically minded, 55% of 11 commands are now located on the View tab. Three commands do not have an equivalent in Excel 2007. Yup, Microsoft dropped the Task Pane and toolbars. Of the two remaining commands, one moved to the Insert tab and the other to the Review tab.

What about the stats for the Edit and File menus?

The Edit menu had 27 commands. Twenty-three commands or 85% made its way to the Home tab. The undo and redo commands were relocated to the Quick Access Toolbar. The Links command was dropped into the Office button menu. Out of the 26 commands in the File menu, the Office button was the big winner receiving fifteen of these. Three commands were dumped. Four were buried real deep … inside the Excel 2007 Excel Options. The Page Setup and Print commands ended up on the Page Layout tab and the Save as Workspace ended up on the View tab.

So far it looks like the menu commands have been reshuffled around quite a bit.

View Menu

Tags: commands, Excel 2003, Excel 2007, Menu, view

Adding Custom Labels to Excel Charts

If you want to create a standard chart in Excel, it’s pretty easy to do. But if you want to customize a chart then things gets a bit harder. Excel offers lots of flexibility when it comes to customizing things but often the trick is finding out how to do it.

Custom labels are a good example of customizing a chart. To see custom labels in action, take a look at the tornado chart in “Excel Tornado Charts” which makes use of Excel’s ability to add custom labels:

It’s not hard to add custom labels. Here’s how:

Position the mouse over the series that you want to label.

Click with the right mouse button. This will open a pop up menu. Move the mouse to the “Add Data Labels” menu item and click with the left mouse button.

You should now see labels displayed next to the series you selected. We want to change these labels to custom labels so move the mouse over the label you want to work with. Click once using the left mouse button to select all labels. Then click again to select the label you want to edit. The chart should like something like this:

Click on the label one more time to start editing. You should now see a caret (flashing line) which indicates that you can change the label. Type in whatever text you want to use. When you’ve finished, press the Esc key. Your chart should now look something like this:

Now you’ve got a custom label. You can repeat the process until the other labels are done.

If you’re not using Excel 2007 then you’ll need to use a slightly different approach. The first thing you’ll notice is that the pop up menu in your version of Excel isn’t the same as the Excel 2007 pop up menu. Your pop up menu will look like this:

In this case you should click on “Format Data Series”. A dialog box will open. When it does, click on the “Data Labels” tab sheet, check the “Value” check box and click the OK button.

This will add value labels to your chart. After this, you can follow the remaining instructions above to customize them.

Tags: chart, custom label, customization, Excel

Edit Menu to Ribbon in Excel 2007

This next article in the menu series looks at the edit menu commands in Excel 2003 and its equivalent commands in Excel 2007.

How to read the table

If this is your first time here, check out the Excel 2007 File menu commands article on how to read the table below.

A number of Excel 2003 commands have keyboard shortcuts e.g. Ctrl + G. The same keyboard shortcuts apply in Excel 2007. It is actually quicker to learn these shortcuts then to memorize how to access the commands from the ribbon.

Edit Menu

Tags: commands, edit, Excel 2003, Excel 2007, Menu

Excel Tornado Charts

Tornado charts are bar graphs. Excel is an excellent tool for producing them.

Tornado charts can be used, for example, to show the results of sensitivity analysis testing or to highlight differences between two groups. An example of a Tornado chart is shown below:

In this example a simple Excel financial model that measures profitability has been re-calculated several times and the results have been used to produce a Tornado chart. The variables that drive the model (number of sales, expenses etc) have been increased or decreased one at a time by 10% of their initial value. The change in profitability has been recorded and then plotted.

Charts can sometimes be a better alternative to showing numerical results in tabular format. Presenting results in a graphical format can be a useful way of letting people see the bigger picture almost immediately whereas a mass of numbers can sometimes be overwhelming and hard to decipher.

Tags: chart, Excel, sensitivity test, tornado

List of Excel 2007 Commands Equivalent to the Excel 2003 File Menu

Office 2007 has been out for over a year now. If you are upgrading from Excel 2003 to Excel 2007, this table of equivalent commands in Excel 2007 will hopefully make the transition to Excel 2007 easier.

This is the first article in the menu series.

How to read the table

The Office button is located in the top left-hand corner. Office button > New means click the Office button then click on the New item in the drop-down menu that opens.

Some commands are now located in the Excel 2007 ribbon. For example, the Page Layout tab is sub-divided into groups, one being the Page Setup group.

Command in the table refers to additional commands accessible via the Excel Options. To access the commands, click on the Office button and then click the Excel Options button. Next click Customize in the left pane and then select All Commands from the Choose commands from drop-down menu. Next locate the relevant command in the window below this drop-down menu and click on Add to add it to the Quick Access toolbar.

File Menu

Tags: commands, Excel 2003, Excel 2007, file, Menu