Excel Zoom

In large worksheets, it is especially useful to be able to view a cell that contains summary data, such as totals. This can become tedious if your totals are at the end of your worksheet, while the data you’re editing is at the beginning, causing you to have to scroll down to be able to see the totals.

If you use Excel’s Watch Window, you can keep your worksheet’s summary data in view while no matter where you are editing within the worksheet.

To do this in Excel 2002 or 2003, right click the cell you want to watch, and select “Add Watch” from the list. The window that appears will give you information about the cell, such as the workbook and worksheet it is located in, it’s cell reference, the value in the cell as well as the formula that is in the cell. If you wish to watch several cells, simply repeat the above steps for each cell you wish to watch. You then can drag this window to any edge of the spreadsheet to “dock” it in place, as you would any other toolbar.

If you’re using Excel 2007, you can still do this, except you can’t simply right click the cell as you would in 2002 or 2003. In Excel 2007, select the cells you wish to watch, go to the Formula tab on the ribbon and select Watch Window. In the Watch Window, click “Add Watch”.

Deleting a cell from the Watch Window is just as easy. Simply select the cell from the Watch Window and click “Delete Watch”.

Note: To work along with this tip, you can download the PivotTable Example file. The download file does not contain the PivotTables referred to below, it simply contains the source data so you can follow the steps to create the same PivotTables.

PivotTables are a useful way to quickly summarize data and display it in an easy to read format. Their biggest problem is that not everyone knows how to use them. Once you learn how to use PivotTables, though you’ll use them all the time.

In the PivotTable Example file, you’ll notice a list of salespeople, who have sold various widgets throughout the year, and for all different dollar amounts. You want a summary of sales by salesperson, location, date, and product.

First you need to get the data into a PivotTable.

• Click somewhere within the data, and click Data | PivotTable and PivotChart Report. If you’re using Excel 2007, click the Insert tab and select PivotTable.
• Ensure the selected range includes A1:F27, and place the PivotTable report in a new worksheet.

Now you have a blank PivotTable, with a list of available fields off on the right hand side of the screen.

We now want to find out which salesperson has sold the most. Drag the Salesperson field to the row area, and the Order Amount to the Data/Values area, depending on which verson of Excel you are using. You should see the following totals:

• Evans – $1,689
• Gordon – $2,008
• Jackson – $595
• Jones – $1,837
• Miller – $1,353
• Nolan – $1,649
• Smith – $3,197
• Snell – $1,131
• Stein – $1,358

To find out who sold the most by quarter, drag the Date field to the Column label area. The grand totals should be as follows:

• QTR 1 – $4,288
• QTR 2 – $3,213
• QTR 3 – $3,939
• QTR 4 – $3,377

To find out which location sold the most, replace the Salesperson field with the Location field in the row area. The grand totals by location should be as follows:

• Mid-West – $1,837
• Northeast – $4,550
• Northwest – $1,726
• Southeast – $3,338
• Southwest – $3,366

Finally, to find out which widget was the best selling one, replace the Location field with the Product field in the row area. The grand totals by product are as follows:

• Widget A – $2,508
• Widget B – $2,492
• Widget C – $1,527
• Widget D – $1,245
• Widget E – $1,561
• Widget F – $1,852
• Widget G – $1,512
• Widget H – $1,447
• Widget R – $673

You can also play around with the location of each field, by adding several fields to the row area, for example, to see which salesperson sold the most of a particular product. Basically, visualize how you want your report to look, and then drag your fields into place in order to get the information you want. Also, instead of summarizing the list based on the “Sum” of the dollar amounts, you can tell the PivotTable to count the number of items. This way you can count how many of Widget B Smith sold (two units).

As you can see a PivotTable can be a powerful tool when trying to summarize large amounts of data. Remember, a PivotTable is just as easy to set up if your data contains 30 or 30,000 rows of data. As long as they are formatted with a header row, and the data is in a consistent format, a PivotTable will have no problems creating useful reports.

If you have a list of data in a column, formatted in a similar manner throughout that you wish to separate into several columns, you can use Excel’s Text to Columns feature to separate this information.

For example, say your list is in column A and it contains a list of customer email addresses. You want to retrieve just the part before the “@”, because that is also their ID in your system. If their email addresses are set up in a similar format throughout (i.e. first, middle, last initials and a number – 4 characters before the @), you can use the Text to Column’s fixed width option. More likely though, your customer’s email addresses are each set up differently (i.e. various number of characters before the @), so in this case, you can use the delimited option, where you specify a character to specify where to separate each field. In this case the character is the “@” symbol.

To do this, select the column with your list, then Data | Text to Columns in pre-Excel 2007 versions. In Excel 2007, select the Data tab and Text to Columns on the ribbon.

Assuming you are using the delimited option, select the Delimited option, and click Next. Under Delimiters, select Other and enter a @ in the box. Make sure no other boxes are checked here. In the Data preview window, you’ll see a line between the email address’ user name and the domain, where the @ symbol would be. Click Next.

You can change the column data format and specify the destination of the data, if you wish. Note: if you don’t want to import one of the columns, click the column so that the information is shaded black with white text, and select the Do not import column (skip) option under Column data format. Click Finish.

If you happen to have data that is all formatted identically, you can use the fixed width option in the first step, and drag a line in the Data preview window so that it separates your data where you want it.

Often times, you’ll do work in one Excel file, that would be useful to show in another Excel file. You can simply copy and paste the data in, but if the data is dynamic (i.e. changes often), you might want to link between the two files.

You can link to a cell in another file just as easily as you can link to a cell in the same workbook, the only difference is that you need to specify the file location of the workbook you are trying to link to.

To explain, we’ll start by showing how to link to another cell in the same worksheet, to another cell in a different worksheet but in the same file, and then to another cell in a different file. Note: in all examples below, we’ll assume that you want to link to cell B1 in whatever worksheet you’re referencing.

First, to link to another cell in the same worksheet, type “=B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the current worksheet.

To link to another worksheet (say, Sheet2) in the same file, type “=Sheet2!B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet2″.

To link to another worksheet in another file (assuming the other worksheet is called Sheet1, and is stored on your desktop, with a file name Book1.xls), type “=’C:Users**YOUR_USERNAME**Desktop[Book1.xls]Sheet1′!$B$1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet1″ stored in the file Book1.xls located on your desktop. Note: Replace **YOUR_USERNAME** with your computer’s logon user name.

Also, if you have the two files open, you can type “=” (without the quotes ” “) into your worksheet and then use your mouse to click on the cell you wish to link to. Excel will automatically fill in the appropriate information.

An easy way to quickly reference a cell or list of cells is to define them with a named range.

To do this, simply select a single cell or a range of contiguous cells, then click the name box next to the formula bar and type the name you wish to assign to those cells and press Enter. Your name must begin with a letter and can’t have any spaces (an underscore “_” is a good substitute for a space).

You can use your named range in several different ways. In the examples below, assume that you have a list of customers in cells A1 through A5, as follows: A1 – Joe Smith, A2 – Jane Doe, A3 – Sam Jones, A4 – Sue Kim and A5 – Mike Rodriguez, that you have named “customers”.

• Formula: You can click in another cell and type =customers. The result will be whatever value is in the named range in the corresponding row. So if you type =customers in cell B2, the result will be Jane Doe, because that is in the corresponding row. The same would be true if your list spanned across several columns.
• Data Validation: If you want to have the value of a particular cell only be a value that is in your list, you can set the Data Validation to only allow those values. Click Data | Validation, then on the Settings tab, select Allow | List, and in the source type =customers. If you check the “In-cell dropdown” box, anytime you click that cell, there will be a little drop down arrow that will allow you to select the values from your list. This list can be placed anywhere in your workbook, and unlike the previous example, has no regard for which row you place the list in.

Adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.

For example, say your worksheet asks a user to input information that later will get summarized in a PivotTable. If the user doesn’t enter the information consistently (i.e. spells out words some of the time, and abbreviates the rest), the information won’t show correctly in the PivotTable output. Adding the drop down list will limit the number of values a user can enter into a cell, and provide consistency to your worksheet.

To do this, follow the steps below.

• Select the cell where you want your custom list to appear (if you want the same list to appear in multiple cells, you can copy the list later).
• Click Data | Validation.
• On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
• In the Source box, type the items you want in the drop down list separated by a comma. Alternatively, you can refer to a range of cells in the same worksheet that contains your list, or refer to a named range elsewhere in your workbook.
• Click OK.

When you select the cell that now contains the data validation, you should see an arrow to the right of the cell. Click the arrow, to display the drop down list.

For a person who has used previous versions of Excel, Excel 2007 can be a little intimidating at first. For starters, there are none of the familiar menu options, and some of the keyboard shortcuts that have become second nature have changed. Don’t let this discourage you though. With a little guidance and familiarization, you’ll find that Excel 2007 is more powerful and easier to use than before.

1. Office Button: This button contains most of the functions found in the File Menu from previous versions of Excel. Commands, such as New, Open, Save, Print and others can be found here.
2. Quick Access Toolbar: The Quick Access Toolbar is fully customizable, and allows you to display commonly used commands. In the image above, there is Save, Undo, Redo, Print Preview and New Workbook.
3. Ribbon: The Ribbon replaces the traditional menus found in previous versions of Excel. The standard ribbon tabs are Home, Insert, Page Layout, Formulas, Data, Review, and View. Excel will automatically add menus as necessary to allow you to work with the content of your worksheet. You’ll notice that there is also a Developer and an Add-Ins tab in the image above. This is because there are macros stored in the workbook, which are shown in the Developer tab, as well as several Add-Ins that are running.
4. Help: The Help button provides one click access to be able to browse Excel’s help topics.
5. Name Box: The Name Box isn’t different from previous versions of Excel, however it is one of the lesser known features found in Excel. This displays the cell reference when selecting a cell (the column and row cross section). You can also type a named range in here. To learn more about naming ranges, take a look at our article Tips – Naming Ranges.
6. Insert Function: By clicking the Insert Function button, Excel will open the Insert Function dialogue box. Here you can search for functions by typing keywords in the search box, or by selecting a category from the drop down menu. The Insert Functions interface is the same as in previous versions of Excel.
7. Formula Bar and Expand Formula Bar Button: The Formula Bar should look familiar to you if you have used Excel before. What’s different in Excel 2007 is the Expand Formula Bar Button on the right. When working with long formulas, this button increases the height of the formula bar, without covering over the data in the first few rows of your spreadsheet. It also enables you to scroll vertically through the formula with the scrolling buttons it displays after clicking the Expand Formula Bar Button.
8. Right Click MiniBar: When right clicking a cell in Excel 2007, you’ll see a new MiniBar in addition to the usual shortcut menu, which displays commonly used editing features. This is makes editing easier, so you don’t have to keep clicking back to the Home tab on the Ribbon in order to format your worksheet.
9. Right Click Shortcut Menu: The shortcut menu in Excel 2007 hasn’t changed much, but also displays with the MiniBar when right clicking.
10. Vertical/Horizontal Split Box: The split boxes in Excel 2007 are not a new feature, but are not very well known. By clicking and dragging either the vertical or horizontal bars, you can split your worksheet into several scrollable panes, allowing you to view different areas of your worksheet at once. To learn more about the split box, take a look at our article Tips – Split a Worksheet Into Multiple Panes.
11. Status Bar: The status bar shows information about Excel. In the image above it says “Ready”, however if you’re saving a file, it will display the status there. The AutoSave feature will also display a status in this location when it is saving your work. The status bar also displays the selected cell statistics, quick views bar and zoom level. The status bar is also customizable. Simply right click somewhere in the status bar to display the customization menu.
12. Worksheet Tabs and Insert New Worksheet Button: The worksheet tabs should be a familiar sight from previous versions of Excel, but the Insert New Worksheet button is a new addition. You can now insert new worksheets with one click of the Insert New Worksheet button.
13. Selected Cell Statistics: In previous versions of Excel, when highlighting several cells with numbers Excel would, by default, show the sum of those numbers here. In Excel 2007, it also displays the average and count of those cells in addition to the sum. For purposes of the illustration above, we’ve edited the image to display the selected cell statistics as if we were to highlight two cells, one with a 5 and the other with a 1. We did this so we could also illustrate the right click menu, that would have otherwise obscured the data in the selected cells.
14. Quick Views: The Quick Views bar allows you to quickly switch between Normal, Page Layout and Page Break Preview views. While these page views aren’t new to Excel 2007, the easy access quick views bar is.
15. Zoom Level: The Zoom Level bar replaces the zoom level drop down menu from previous versions of Excel. You can change the zoom by dragging the arrow left or right to a minimum zoom of 10% or a maximum zoom of 400%. Alternatively, you can click the + or – buttons to increase/decrease the zoom level by 10% with each click.

You can split a worksheet area into vertical and/or horizontal panes so that you can click inside any one pane and scroll to locate information in that pane while the other panes remain in place.

Drag a split box (the small box at the top of the vertical scroll bar, or at the right end of the horizontal scroll bar) in the direction you want the split to appear. To remove the split, move the pointer over the split until the pointer changes to a double-headed arrow, then double-click the split.

If you need to fill cells with sequential text such as months of the year, days of the week, or text plus a number such as Quarter 1, Quarter 2,…, use the fill handle to quickly create labels for the cells simply by typing the first item in the series.

For example, you would type January in your first cell, then hover the mouse over the bottom right hand corner until it turns into a black cross, then click and drag the fill handle from the cell containing January until you have all the monthly labels you need.

When you enter a cell reference into the formula bar, you can press the F4 button to make the reference either absolute, mixed, or relative. This helps your formula work as expected when you copy the formula to another location

• Absolute references look like this: $A$1, where the dollar signs ($) indicate that when copying a formula to another cell, Excel should not change the reference to cell A1.
• Mixed references look either like $A1 or A$1. The dollar sign ($) in $A1 means that if you copy the formula, it will always reference column A, however if you copy the formula down, it will change the row number relative to its new location. The dollar sign ($) in A$1 does just the opposite. Copying a formula will always reference row number 1, however it will change the column letter relative to its new location.
• Relative references look like this: A1, where there are no dollar signs ($). This indicates that both the column letter and the row number will change when copying a formula relative to the new location.

Always check to see if your formula is using absolute, mixed or relative cell references prior to copying them to another location.