Tag Archives: excel 2010

Excel 2010: The Watch Window

Data changes result in recalculations of totals which are not always visible, depending on your spreadsheet layout. To keep an eye on how these totals are being affected without dashing around your spreadsheet, collect the important totals into a Watch Window. You can then see the collective effect of your new data from wherever you are in your file.

To add a cell or cells to this window, select the Formula tab, Watch Window, and click the Add Watch button. Select the cell or cells to be watched and click Add. Continue by adding others in the same way.

The Watch Window

Excel 2010: Saving In The Old 2003 Format

Whilst everyone is changing from the old 2003 version of Excel to the new 2010 version, some people will still need to provide spreadsheets in the older version. If this is for a single or occasional file, in the Save As dialog box simply change the File Type in the bottom box to Excel 97-2000 Workbook, beneath the File Name.

Save In Old StyleIf you are saving in the old format on a regular basis, it will be simpler to change the default file format. Click on the File tab and select Options, and on the Save page, change the default File Type at the top. When everyone is converted, change this back to Excel Workbook.

Default Saving screen

Excel 2010: AutoCorrect Options

AutoCorrect entries simply replace a text error or shortcut with the correct or longer version. For example, typing “teh” will automatically correct to “the”. This feature has a long list of entries which you may wish to change, remove or add to.

Select the File tab, Options and go to the Proofing tools page. Click on AutoCorrect Options to see the dialog box and list, and the Replace and With boxes to add your own. Whatever is typed in the Replace box in the future will be automatically changed to your With entry, unless you switch the feature off with the Replace Text As You Type check box half way down the page.

AutoCorect list If this feature is left switched on, certain Exceptions can be added to the rules with the button on the right, as these examples show.

AutoCorrect Exceptions

Excel 2010: Creating Custom Lists

There are many built in lists in Excel, numerical, date/time, text and numerical, and some text entries for month and day (try typing Jan in a cell and replicate the sequence down with the bottom right corner of the cell). For sequences that are not immediately available, Excel gives you the option of creating your own customised text lists.

Go to the File tab and select Options. On the Advanced page (tab at the left), scroll down to the General section where you see the Edit Custom Lists button. Click to open the dialog box.

Adding a custom listIf you have a list already in a spreadsheet, select the cells first. Now go to the Custom Lists dialog box and use the Import button to save retyping the entries.

Excel 2010: Using Named Ranges and the Name Manager

For larger spreadsheets, Range Names are more convenient than scroll bars.  To name an individual cell or block of cells highlight the cell or cells first, click on the Name Box, type in an appropriate name, and press the Enter key. (Range names do not accept spaces.)

Range Name list and selected areaTo find the block again, use the drop-down list in the Name Box and click on the name. Alternatively use the GoTo command (Ctrl+G or F5). Note: Range names can also be used in formulae instead of their cell reference equivalent.

GoTo windowTo edit or delete range names, use the Name Manager from the Formulas tab. Note that the delete option only deletes the range name, and not the contents of the cells in that range.

Excel 2010: Auditing Tools

There are times when you inherit a spreadsheet and wonder how it works. You can identify the cells with formulae, but still have difficulty tracing the sources or subsequent calculations. This is where Excel’s Auditing Tools can help point you in the right direction (literally).

Tracing Precedents and Dependents

Select a cell with a formula. To find the cells that feed into your chosen calculation, click on the Formulas tab and choose Trace Precedents. The blue arrows or marked areas indicate which cells are used in the calculation of this formula. Choose Trace Dependents to indicate which cells use this cell in further calculations.

Blue arrows on a spreadsheetIt is worth knowing that these are multiple-level tools so you can take the calculations to the next stage and beyond. For example, if the original data cell is used in a calculation, Trace Dependents will point the cell out. If this is used in a further calculation, click Trace Dependents again (you don’t need to move from that cell) to see the next calculation, and so on. Just keep clicking until you reach the end of the route.

For 3-D spreadsheets, a sheet icon will appear if the precedents are coming from a number of other sheets. Double click the arrow to open the sheet list where you can select a particular location to view further (double-click).

3D spreadsheets list

Using Auto Sum

Let’s look at an example using a formula that provides a total amount.

excel-formulas-summing

In the total row, we want to display the sum of all household expenditure. Make sure that the cell you want to contain the total is active and then click Home > Editing > AutoSum. Because the active cell is positioned directly below a column of numbers, Excel 2010 guesses that you want to sum them and places a selection around them (see A below).

excel-formulas-autosum

B is called the Formula Bar and it displays the content of the active cell, which in this case is a formula. C is the active cell and contains the formula. Let’s look at the structure of a formula.

You can see that the formula starts with a “=”.  All formulas must start with “=”. The next part is SUM, which you can guess represents the SUM function. The contents of the brackets are called arguments and they define the range of cells that we are summing – in this case cells B2 to B7. This range is defined as B2:B7. Autosum is a quick and easy formula to use that automatically identifies its input variables. Let’s now look at a formula we can set up manually.

Defining Your Own Formula In Excel

Suppose we have the following example that details price, VAT and total price.

excel-define-your-own-formula

As you can see, we start with a price before VAT and we need to calculate first the VAT and then the total price. Let’s calculat the VAT first, based on a VAT rate of 17.5%. The formula therefore is VAT = 17.5% x Price. Make cell B2 active and in it type
=17.5%*A2
Excel knows how to handle percentages so you don’t need to convert 17.5% to 0.175 for the calculation. When you press enter, Excel calculates the VAT to be 43.75. We have dealt with the first VAT amount, but what about the rest? There is a quick and easy way to apply the formula you just created to other cells. With cell B2 active, position the cursor over the bottom right corner so that the cursor becomes a ‘+’. When it does, drag downwards to select all cells that should use the formula (down to cell B6).

excel-apply-formula-to-other-cells

When you release the mouse, Excel applies the VAT formula to the selected cells and calculates their values. That’s the VAT for all rows take care of. On to the total price.

Total price = price + VAT

We can define that as a formula by typing into C2 the following

=A2+B2

When you press Enter Excel calculates the sum of A2 and B2 to give 293.75. And we’ve already seen how we can to apply that formula to the remaining cells. The finished spreadsheet should look like this.

excel-formulas

Paste Options

Microsoft Excel 2010 normally copies all the information in the range of cells you select when you paste the data. Use Excel’s Paste Special command to specify other options, such as pasting only the cell contents (without the formatting) or only the formatting (without the cell contents).

To paste particular parts of a cell selection, click the Paste button arrow on the Ribbon’s Home tab. Then, click Paste Special on its drop-down menu to open the Paste Special dialog box.

Paste only some of a copied or cut cell's properties with Paste Special.

Paste only some of a copied or cut cell’s properties with Paste Special.

You can specify which parts of the current cell selection to use by selecting the appropriate Paste Special options:

  • All to paste all the stuff in the cell selection (formulas, formatting, you name it). This is what happens when you paste normally.
  • Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.
  • Values to convert formulas in the current cell selection to their calculated values.
  • Formats to paste only the formatting from the current cell selection, without the cell entries.
  • Comments to paste only the notes that you attach to their cells (kinda like electronic self-stick notes).
  • Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command.
  • All Using Source Theme to paste all the information plus the cell styles applied to the cells.
  • All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.
  • Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.
  • Formulas and Number Formats to include the number formats assigned to the pasted values and formulas.
  • Values and Number Formats to convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.
  • All Merging Conditional Formats to paste conditional formatting into the cell range.

When you paste, you can also perform some simple math calculations based on the value(s) in the copied or cut cell(s) and the value in the target cell(s):

  • None: Excel performs no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste. This is the default setting.
  • Add: Excel adds the values you cut or copy to the Clipboard to the values in the cell range where you paste.
  • Subtract: Excel subtracts the values you cut or copy to the Clipboard from the values in the cell range where you paste.
  • Multiply: Excel multiplies the values you cut or copy to the Clipboard by the values in the cell range where you paste.
  • Divide: Excel divides the values you cut or copy to the Clipboard by the values in the cell range where you paste.

Finally, at the bottom of the Paste Special dialog box, you have a few other options:

  • Skip Blanks: Select this check box when you want Excel to paste only from the cells that aren’t empty.
  • Transpose: Select this check box when you want Excel to change the orientation of the pasted entries. For example, if the original cells’ entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row.
  • Paste Link: Click this button when you want to establish a link between the copies you’re pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.