Excel: Relative vs. Absolute Cell Reference

There are two types of cell references: relative and absolute.

Relative and Absolute references behave differently when copied into other cells.

Relative references change (relatively) when a formula is copied to another cell. In other words, they change based on the formula. So, if you copy a formula referencing Cell A1 and copy the formula to Row 2, the "A1" reference will change to "A2" since you copied the formula down a row.

Absolute references, on the other hand, remain constant no matter where they are copied. In other words, they will NOT change when you copy a formula to other rows or columns. This type of cell reference is useful when budgeting, for example. You can see what happens if revenues increase by a specific percentage. When formulas are based on the percentage cell, you can change the percentage using a single cell and all other formulas referencing this cell will update automatically according to the new number in that cell.

Watch this brief tutorial to see exactly what I mean.

Excel Tip: Sheet Types

For those of you who use Excel, I'm always trying to show tips that are helpful and fun at the same time.  Did you know Excel has built-in Sheet Types?  This short video will show you this fun Excel Tip.

Enjoy!

Excel Properties

Have you ever sent an Excel spreadsheet to someone whom asks you details on when the worksheet was created or other details?

Well, this tip shows you how to add some basic contact and worksheet information for yourself and others.  Excel Properties allows users to search for spreadsheets and access some other details of the file including the Author, Company, and like information.  Excel 2007 allows "Comments" to be added for further reference and details.

To add information to the Excel Properties, follow these steps.

Click on the "Office Button," hover to "Prepare," and click on the "Properties" option.

HINT:  For Excel 2003 and earlier (and for 2010), click on the "File" Menu/Tab and select "Properties."

In Excel 2007 and 2010, the "Properties" window opens above the worksheet fields.

Information such as phone number, email, formulas, or other information may be added for reference.

In Excel 2003 and earlier, the "Properties" window opens as a pop-up window.

Notice that the "Properties" window prefills the "Author" field as the user of the said computer.



In Excel 2007 and 2010, click on the "Document Properties" drop-down and select "Advanced Properties" to add further information.

This window appears similar to Excel 2003 and earlier versions.

If this window is chosen, you will need to click on the "OK" button when completed.

Simply save the workbook and the added information is saved with the workbook.

When sending Excel spreadsheets to others, this information is stored for their reference.

Excel Quick Tip

Press "CTRL ~" to display all formulas.

Not only does this provide a quick view of the formula in the first cell, but it shows all formulas in the entire spreadsheet.  This can be used to quickly see information without having to scroll to the cell and view the formula in the formula bar.

Press "CTRL ~" again to hide the formulas and return to the standard view.

Excel 2007 Watch Window

If you're like many Excel fanatics, you probably deal with large spreadsheets and multiple cells containing formulas.

Excel 2007 has a great new feature that lets you "watch" cell contents as they change.  If numbers are added, subtracted, and changed, these specific cells are probably something you'd have to remember to review in previous versions of Excel.

To turn on the "Watch Window," follow these steps.

Highlight the cells you want to "Watch."

Click on the "Formula" tab and select the "Watch Window" button in the "Formula Auditing" group.

Click on the "Add Watch" button.

Since the cells were highlighted, simply click on the "OK" button and the cells will be added to the "Watch Window."



Now when you make changes to the spreadsheet, the "Watch Window" shows you the changes to the cells you added to the list.

If you accidentally close the "Watch Window," simply click on the "Formula" tab and then the "Watch Window" button to open it again.

Excel 2007 Quick Tip

Many of you have seen the improvements made in Excel 2007; but most users have still not learned all of the handy new features.

One of these new features is located in the "Recent Documents" area.

We've all seen the recent documents list and I've shown ways to change the number of recent documents as well as turning them on and off.

But, did you know you can "Pin" specific documents to the "Recent Documents" list?

To do so, simply click on the "Office" button to bring up the "Recent Documents" list.

Move the mouse to the spreadsheet you want to keep on this list permanently and click on the "Pin" icon on the right side of the spreadsheet name.

See image below.



The spreadsheet will remain on the list as more spreadsheets are added.

HINT:  This is an Office 2007 feature and may be used in Word 2007, PowerPoint 2007, etc.

To "unpin" the spreadsheet, simply click on the "Pin" icon again to "unpin" the spreadsheet from the list.

Excel Cell Reference Tip

By default, Excel uses the "A1" format when referring to cells.  This means that the "A" is the Column and the "1" is the Row that is being referred to.  Most Excel users understand this; but some spreadsheet programs do not use this referencing.

Instead, other programs use the "R1C1" format when referencing cells.  Excel allows for this format as well.

To specify the format you want to use, follow these steps.

Excel 2007:

Click on the "Office" button and select the "Excel Options" button.

Select the "Formulas" tab on the left menu.

Check the "R1C1 reference style" checkbox in the "Working with Formulas" section and click the "OK" button to save the changes.



Excel 2003:
Click on the "Tools" menu and select the "Options" menu item.  Follow the above instructions from here.

Excel 2010:
Click on the "File" tab and select the "Excel Options" button.  Follow the above instructions from here.

Notice the Column Headers are now numbers instead of letters.



NOTE:  If you prefer the "A1" formatting and need to change Excel back to this preference, simply follow the above steps and uncheck the "R1C1" checkbox.

Excel Quick Tip

If you've ever needed to copy information in an Excel spreadsheet (who hasn't, right?), then this Quick Tip will become one of your favorites.

Let's say you have information in Cell A5 that needs to be copied into Cell A6.

With the cursor in Cell A6, simply press "CTRL + D."

It's that simple!

How Green is Your Excel?

Have you seen the little green triangle in the upper left corner of a cell in Excel?

What is this green triangle?

It simply tells you that something is wrong with the formula in the cell.  It may not create an error message; but when you click on the green triangle, you'll see a yellow exclamation point.

Click on the drop down next to the yellow exclamation point and Excel will provide some possible answers.

If you have Excel 2007 or 2010, the yellow exclamation point provides you with a "trace error" function which helps follow the formula to find out where there may be a mistake.

Understand these "smart tags" like the green triangle and you'll be able to correct errors without a lot of frustration.

Change Gridline Colors in Excel 2007

This is a happy little feature that was added in Excel 2007.  Now you have the ability to change gridline colors in Excel 2007.

To do so, follow these steps:

Click on the "Office" button and click on the "Excel Options" button.

Select the "Advanced" tab from the left menu and scroll to the "Display Options for this Worksheet" area.  The gridline color option is located in the second section of "Display Options."

Click on the drop down to select a color.  Choose any color you want.

Click on the "OK" button to save the changes and exit the "Excel Options" window.

The gridline colors are now as you selected.

HINT: This may be helpful for situations where you have changed the background color to something light blue; thus, making the gridlines non-viewable.

Here's the caveat: only the selected worksheet is changed.  To change each sheet, you must select each sheet name from the drop down by the "Display Options" header and then change its corresponding color.

NOTE: Each new worksheet will display the gridline colors as the default light blue color.  You are not changing the default by selecting this option.

It's that simple to change gridline colors in Excel 2007.

Change Excel to International View

Intrenational users sometimes view things differently than here in the US.  But, did you know you can change Excel to International View?

Here's how:

Click on the "Tools" menu and select "Options."

Click on the "International" tab and select the "Right to Left" checkbox.  If you want the current spreadsheet to change as well, click on the checkbox for "View current spreadsheet right to left" also.

All future spreadsheets will change to "Right to Left" view with Cell A1 on the right side of the screen as well as the row numbers.

To change it back, simply uncheck the above selections and click on the "Left to Right."

As always, make sure you click on the "OK" button to save the changes and close the "Options" window.

It's that simple!

How do I use AutoFilter in Excel?

Have you ever searched an entire Excel spreadsheet for data that matched certain criteria?

Use the AutoFilter in Excel and you will be able to find data much quicker.

To do so, simply follow these easy steps:

Excel 2007:
With your list open, click on the "Data" Tab on the Ribbon.  On the Data Tab, simply click the "Filter" button.

Notice your data Headers all have Drop Down Arrows next to them.

Now, simply click on the Drop Down Arrow of the Header (or Column) that contains the data in which you wish to search.

You may search for a specific item from the Drop Down list OR you may click on "Number Filters" from the Drop Down and another sub-menu will appear giving you options to search on items Greater than a particular number, etc.

Excel 2003:
Click on the Data Menu and choose "AutoFilter" and then follow the steps above.

It's that simple!

How to Print Excel Formulas

I have many students that ask me how to create a list of the most used Excel Formulas.  There are a few ways... 

If you want a list from Microsoft Help, just open your help by pressing F1 or use your Office Assistant.  Search for Formulas and Print the Help page.

But sometimes this list isn't entirely what you want.  So, you can simply create your own list of formulas and print your list for future reference.

To do so, follow these instructions.

Type a list of the common formulas you use (or use an existing spreadsheet that someone else has created).

Click Tools | Options.

Click on the VIEW Tab.

On the View Tab, check "Formulas" in the Windows Options area of the Tab.

Click OK and you will return to your spreadsheet with the Formulas showing, instead of the results.

It's that simple.

Add Row Numbers in Excel

There are any number of formulas you can use in column A that will return a row number.

Perhaps the easiest is to use the ROW function, like this:

=ROW( )
This formula returns the row number of the cell in which the formula appears.

If you want to offset the row number returned (for instance, if you have some headers in rows 1 and 2 and you want cell A3 to return a row value of "1", then you can modify the formula to reflect the desired adjustment:
=ROW( )-2

Of course, the ROW function isn't the only formula that will perform this function. Look for more Go Ask Debbie Tips on using Excel formulas and functions directly at Go Ask Debbie.

Transpose Columns in Excel

Sometimes data that you have in Columns may look better in Rows, or vice versa.

To transpose Columns to Rows (or Rows to Columns) in Excel, is very easy. To do this, follow these simple steps:

1. Highlight the data you wish to transpose.

2. Click the Copy button.

3. Click into an empty cell (Note: it must be in a location separate from your current data or you may run into problems).

4. Right-click and choose "Paste Special".

5. On the Paste Special sub-menu, click the "Transpose" check box and click OK.

Remember, for this to work your data must truly be in a data format. If there are Blank Rows or Columns and/or the data is all in One Column or Row, the Transpose feature will not know what to do with your data.

Join Cells in Excel

Do you need a Full Name field to import into a particular program? But, you only have First Name and Last Name fields in Excel? There are many times when you need to Merge or Join Cells in Excel - this tip shows you how easy it really is:

Insert a New Column.
Type =CONCATENATE(Cell1,Cell2,etc)

Using the formula above, here is an example of the results:
Cell1 Cell2 Cell3 RESULTS
Go Ask Debbie GoAskDebbie

If these are the results you wish, simply Copy the formula down the column to include all rows you wish.

But, let's say you need a Space or a Comma between each of the cells once they have been merged. To do this, type your formula as follows:

=CONCATENATE(Cell1," ",Cell2," ",etc)

This would create the following result from the above scenario "Go Ask Debbie". Notice now there are spaces between the cell contents.

Get creative this can help you create many different types of results.

Excel Scaled Printing

Excel spreadsheets can get very large, very quickly. And, how do you fit all of that information on one page? Well, you may need more than one page, but Excel allows you to scale your data as much as you need.

To scale your spreadsheet, follow these steps:

Choose File Page Setup

Select the "Page" Tab (Should be the Default Tab)

In the Scaling area of the dialog window, specify how you would like to scale the document. You have the option to scale to a percentage or fit to a certain number of pages that you wish.

Once you have chosen the Scaling options, I recommend to click on the Print Preview button to make sure the spreadsheet will print how you would like.

Should you need to make changes, simply Close the Print Preview window and you will return to the Page Setup to make any necessary adjustments prior to printing.

Excel Conditional Formatting

Excel includes a powerful feature that allows you to dynamically change the formatting of individual cells based on the results being displayed in that cell. For instance, you could make the text in the cell larger and red if a result is less than a certain threshold. Likewise, you could color the background of a cell based on the result of a formula.

To take advantage of conditional formatting, follow these steps:

Enter your cell formula as you normally would.

Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.

Use the controls in the dialog box to specify the threshold or ranges you want to set for formatting to be changed.

Click on the "Format" button to edit the formatting you wish to appear when the condition is True.

Click on OK to close the Format Cells dialog box.

Click on the Add button and define more conditions (and formats), if desired.

Click on the OK button to close the Conditional Formatting dialog box.

You should now see the formatting in all of the cells that meet the condition.

Naming Excel Worksheet Tabs

If you have large Excel spreadsheets with multiple Tabs, it may help you to Name each Tab with a name that is relative to the data contained within the Tab.

As a default, Excel opens three (3) worksheets. Each worksheet has a Tab at the bottom of the screen named "Sheet1", "Sheet2", and "Sheet3". Obviously these mean nothing if you have data on Sheet2 that is February's data.

To rename the Tabs, simply follow these steps:

1) Double-click the Tab and Replace the name "Sheet1" with whatever you would like it to be. For example, "JAN" for January's data.

2) Once you have typed the new name, simply click ENTER.

Now the Tab makes more sense and will help you move amongst the Tabs quicker.