Wednesday, August 11, 2010

Is Your Excel on Automatic or Manual?

Is Your Excel on Automatic or Manual?

iconcarAutomatic or manual makes me think of cars, and choosing a transmission type, but an Excel workbook can also be on Automatic or Manual calculation. And just like a car, it's safest if you know what type of Excel calculation mode you're driving, before you head for the information highway.
In a car, you can tell if it's a manual transmission, if there's a clutch pedal, to the left of the brake pedal. In Excel, it might not be obvious whether the calculation mode is set for Automatic or Manual.

Add a Calculation Indicator

To help avoid problems, you can add Automatic and Manual to the Quick Access Toolbar (QAT) in Excel. This has two benefits:
  • you can quickly see the current calculation mode,
  • and you can easily change the current calculation mode.
QATAutoMan 
Those check boxes are really helpful when working with big, complex files. I can easily turn calculations to Manual while making changes to the file, and then back to Automatic to make sure everything is working correctly.

Why Does Calculation Change to Manual?

Occasionally, a client will call about problems with an Excel file, that was working perfectly the day before. Sometimes the problem can be traced to the calculation setting. Somehow, it switched to Manual, and the client doesn't know how or why.
Even if you never change your calculation mode, it can be changed without your knowledge. The calculation mode setting is affected by the first workbook that's opened during an Excel session.
So, if a co-worker sends you a workbook that was saved in Manual calculation mode, and you open that workbook first thing in the morning, it could affect the rest of your Excel files. With the Automatic and Manual settings visible on your QAT, you'll have a better chance of noticing the problem.

Quickly Hide Excel Rows and Columns

Quickly Hide Excel Rows and Columns

In some of my Excel workbooks there are calculation rows or columns that are required for producing the end result, but users don’t need to see them.
I can manually hide these rows and columns, then unhide them if I need to check a calculation, or adjust a formula.
To make it easy to hide things, I mark the rows and columns with an X.
HideColMark
In row 1, the only entries are the X marks on the columns that should be hidden. In column A, the only entries are the X marks on the rows that should be hidden.

Select the marked columns

Click the row button for row 1, to select the entire row.
On the Ribbon, click the Home button
In the Editing group, click Find & Select, then click Go To Special
(Note: In Excel 2003 and earlier versions, click Edit > Go To, then click the Special button)
GoToSpecial
In the Go To Special dialog box, click Constants
Uncheck all the boxes except Text, then click OK
GoToSpecialConst

Hide the Marked Columns

With the marked columns selected, click the Format command on the Ribbon’s Home tab
Click Hide & Unhide, then click Hide Columns.
Note: In Excel 2003 and earlier versions, click Format> Column > Hide.
HideColumns

Hide the Marked Rows

Use similar steps to hide the marked rows. Select column A and go to the Constants that are text, then use the Hide Rows command.

Automate the Steps

If you frequently hide and unhide the columns and rows, record a macro as your perform the steps. Then, run that macro to automatically hide all the marked rows and columns.

Quickly Create Named Ranges in Excel

Formatting Converted Workbooks in Excel 2007

One of the new features in Excel 2007 is live preview when you select a different font or theme on the Ribbon. For example, here’s how my worksheet looks now:
Format2007_01
The default font for the cells is Calibri, and I’ve added bold in the first row. I’m not sure if the Calibri font is the best choice, so I’d like to see how the data would look in a different font.

Select a Different Font

I select column H, then click the drop down arrow for Fonts on the Ribbon’s Home tab. The live preview shows the selected cells in each font as I point to it in the list. If I find a font that I like better, I can click on it to change the font for the selected cells. None of the other cells are affected.
Format2007_02

Select a Different Theme or Theme Font

In most cases, I wouldn’t just change the font in one column or a few cells. To keep the workbook from looking like a ransom note, I’d usually change the font for the entire workbook.
In Excel 2007 I can do this by choosing a new Theme or by selecting a different Theme Font.
  1. On the Ribbon’s Page Layout tab, click the drop down arrow for Themes or for Fonts, then point to one of the options.
  2. The live preview affects all the cells that use the Calibri font, instead of just the selected cells.
  3. Click on one of the options and all the cells with Calibri font are changed.
Format2007_03

Formatting Converted Workbooks

Slowly, I’m converting some of my old Excel files, like the price list shown below, to Excel 2007 format. When I use the Theme or Theme Fonts drop down list, the live preview doesn’t work. The data is still shown in Arial Narrow, which was the default font in Excel 2003.
Format2007_04
To make this worksheet fit the current theme, I’ll change the fonts to Calibri and Cambria:
  1. Click the Select all button at the top left of the worksheet, to select all the cells on this worksheet.
  2. Then, on the Ribbon’s Home tab, select the Calibri font.
  3. To format the first row as Headings, select Row 1 and apply the Cambria font.
Now, any cells that are formatted with one of the Theme fonts will show a live preview when selecting a different Theme or Theme Font.
Format2007_05

Merge Styles Tip

Quickly Create Named Ranges in Excel

Quickly Create Named Ranges in Excel



In an Excel workbook you might have a worksheet that contains several lists that you use as the source for data validation dropdown lists. For example, this worksheet has a list of countries, and lists of regions within those countries.
NameCreateRepeat
If each list has a heading, you can quickly create named ranges from the lists.

Create the First Named Range

Select the heading and the items in the first list that you want to name.
RangesSelected
On Excel’s Ribbon, click the Formulas tab
In the Defined Names group, click Create from Selection
(Note: In Excel 2003 and earlier versions, click Insert > Name > Create)
NamesCreateCmd
In the Create Names dialog box, add a check mark to Top row, remove any other check marks, then click OK.
NamesCreateTop

Name the Remaining Ranges

To name the next range, select its heading and items
On the keyboard, press the F4 key.
Repeat for all the remaining ranges.
_______________