Excel Zoom

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.

Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.

Right click the cell where you want to insert the comment, and begin typing in the box that appears. Click off the cell and you’ll see a little red triangle in the upper right hand corner of the cell where you just inserted a comment. This is a visual cue to let a user know that there is a comment in that cell.

To view that comment, simply hover over the cell, or right click the cell and select Show/Hide Comments. Hide the comment by right clicking the cell again and selecting Hide Comment.

To edit a comment, right click and select Edit Comment.

To delete a comment, right click and select Delete Comment.

Excel 2007 allows you to do all the previously mentioned actions on the Review tab under the Comments group.

There are several different types of charts found in Excel. All of which are used to display information in a way that makes it easier to interpret data.

• Column charts are best suited to compare distinct object levels in a vertical format.
• Line charts compare trends over even time periods, but are not necessarily used to show totals.
• Pie charts show the relative size of a particular data point as a part of the whole.
• Bar charts are best for comparing distinct object levels ina horizontal format. It is similar to a column chart, just flipped on its side.
• Area charts show how individual volume changes over time in relation to a toal volume.
• Scatter charts compare trends over uneven time or measurement intervals. This type of chart is useful to spot trends in a set of data.

Often times, a project manager will be given a project’s start date and the number of days it should take to complete. Considering, however, that most people work Monday through Friday, you can’t simply add a certain number of days to the starting day’s date to come up with your ending date, because that wouldn’t take into consideration weekends and holdiays.

Using the formula =WORKDAY() allows you to specify a beginning date, the number of work days until the ending date, and any holidays that might fall between the start and finish of the project.

If you wish to specify holidays, enter the holiday’s date value in the formula (i.e. Memorial Day in 2009 is May 25, 2009, or in Excel’s number format, 39958). This can be determined by using Excel’s =DATE() formula.

More on these formulas can be found on our Wiki page.

• WORKDAY Wiki Page
• DATE Wiki Page
• NETWORKDAYS Wiki Page