CREATE THE FUTURE

Tools to create the future you envision!     

What's New?

 

 | About Us | Contact Us | Site Map |     

Home

 

Weekly Picks

Cool Websites
Publications
Key Trends
Useful Resources
Tech Tips
 
Picks Archives
 
 

Bookmark and Share

Excel Tips

View an alphabetical listing of Excel Tech Tips

Adding Data Forms to the Excel 2010 Quick Access Toolbar

Those of you that enjoyed using the feature called Data Form in earlier versions of Excel may be unhappy when you can’t find it anywhere in the new 2007/2010 Ribbon interface.  But you CAN add it to the Quick Access Menu at the top of the Excel 2010 screen, and here’s how:

bullet

Click the File Tab in the upper left corner of the Excel 2010 Screen

bullet

Click the Options button at the bottom of the Office window to display the Excel Options dialog box

bullet

Click Quick Access Toolbar from the Options list

bullet

Select Commands Not in the Ribbon from the Choose commands from drop-down list

bullet

Select Form

bullet

Click Add and then OK

To use the Data Form click within the list range and click the Data Form button.
 

Display the First Name in Excel

Have you ever received an Excel list of names with the first and last name in a single cell?  If you would like to display just the first name, try this:

For example, if your list is in Column A, type this formula in column B: =LEFT(A2,FIND(" ",A2)-1)

Copy this formula down to each row containing the names. But remember, the first names in column B are still a formula and can’t easily be moved or copied.  You may want to copy the cells containing this formula back to their original position using Paste Special to convert the formula into a value:

bullet  Select the column by clicking the column name 
bullet  Choose Copy
bullet  Right click and choose Paste Special from the short-cut menu
bullet  Choose Values and click OK

This tip works in Excel 2007 and 2010. You can also use Text to Columns to accomplish this.
 

View Formulas that Reference a Cell in Excel

Last week we covered how to view cells referenced by a formula.  Another useful tool when debugging a worksheet is doing the reverse -- viewing formulas referenced by a cell.  Here’s how:

bullet Select the cell
bullet Press Ctrl + ] (Ctrl + close square bracket)
bullet Excel moves the to the first formula that references the cell
bullet Each time you press Enter the selection moves to the next formula that references the cell

This tip appears to work in the new Office 2007 or 2010 as well as earlier versions.
 

View Cells Referenced by a Formula in Excel

When debugging a worksheet it can be very useful to look at each cell referenced in a formula.  Here’s how:

bullet

Select the cell containing the formula

bullet

Press Ctrl + [  (Ctrl + open square bracket)

bullet

Excel highlights all cells referenced by the formula and moves the current selection to the first of the referenced cells

bullet

Press Enter and the selection moves to the next referenced cell

bullet

Each time you press Enter the selection moves to the next referenced cell

In the following example I selected cell B11 which contained the formula =B10+B7+B4. When I pressed Ctrl + [.cells B10, B7, and B4; were highlighted and the current selection was B10.

This tip appears to work in the new Office 2007 or 2010 as well as earlier versions.
 

Using the Data Analysis ToolPak in Excel 2007/2010

Most people are not aware of the Data Analysis ToolPak in Excel. This feature is disabled by default. To use the Data Analysis ToolPak you need to be familiar with Engineering and Statistical terminology. To enable it:

bullet Click the File tab (2010) or Office Button (2007)
bullet Click the Options button at the bottom of the menu
bullet Click Add-Ins in the left pane
bullet Click Go
bullet Select Analysis ToolPak
bullet Click OK

You can now access the ToolPak from the Data tab in the Analysis group.
 

Automatically Tab from Cell to Cell In Excel 2007/2010

If you have a spreadsheet with several data input cells and would like to automatically jump from cell to cell using your tab key, you can use the Protect feature to accomplish this.  In a previous tip we explain how to Protect Cells in Excel 2007/2010.

After you have:

bullet

Unlocked the cells that can be changed and

bullet

Protected the worksheet

You can simply use your tab key to automatically move from cell to cell.  This can greatly simplify some types of data input and you'll learn about worksheet protection in the process!
 

Choosing Between Excel and Access for your Database

A question I hear frequently is how to choose between Excel or Access for a database. The following resources should help you answer this important question:

bullet Microsoft has a free online tutorial Choose between Access and Excel that will help you understand the terminology and make this decision by asking the right questions up front.
bullet Using Access or Excel to manage your data compares the benefits of each program, discusses when to use Access, when to use Excel, and when to use Access and Excel together, also on Microsoft.com.
bullet Another Microsoft article Top 10 reasons to use Access with Excel demonstrates how often the best solution is to use the 2 programs together.
 

Using Date Functions in Excel 2007/2010

There are many ways to use the Date functions in Excel.  Previous Tech Tips have included:  Calculate a Person's Age in Excel; Calculate Remaining Days in the Year; and Calculate the Days, Months or Years between Dates in Excel.

A lesser known date function is NETWORKDAYS, which returns the number of work days between two dates. For example:

The format for this function is: NETWORKDAYS(start_date,end_date,[holidays]).  Holidays is optional.

The following tutorials can help you learn to use of the Date functions in Excel:

Excel 2007 / 2010 Date Functions: Working with Dates in Excel from www.about.com

Microsoft Excel 2007 to 2010: The Date Function in Excel from www.homeandlearn.co.uk
 

Selecting a Range in Excel 2007/2010

In Excel, a range is a group of connected cells. When working in Excel you often need to select a range.  Here is a simple trick for quickly selecting a range:

bullet Click anywhere within the range
bullet Press Ctrl + A
bullet The range is selected

If the worksheet does not contain data, Ctrl + A selects the entire worksheet.  Another way to select the entire worksheet is to click the button in the upper left corner of the worksheet

Move or Copy Sheets between Workbooks in Excel 2007/2010

bullet Open the sheet you want to move or copy
bullet Click the Home tab on the Ribbon
bullet In the Cells group, click Format

bullet Under Organize Sheets, select Move or Copy Sheet
bullet Choose where you want the sheet to be copied or moved to
bullet Be sure to select the create a copy box if you do not want your sheet moved
 

Add Text to Displayed Numbers in Excel 2007 or 2010

To add text to a number in a cell:

bullet

Select the cells you want to add text to

bullet

From the Home tab on the Ribbon, in the Cells group, click

bullet

Select Format Cells from the drop-down menu

bullet

Select Custom from the Category list

bullet

In the Type box, select the default value General

bullet

After the word General, enter a space and a quote, next enter the word you want to display and another quote. For example, General "Pounds"

bullet

Click on OK

The format you create will put the word after any number you enter into the cell, but Excel will still treat the value as a number, not text.
 

Create Equations in Excel 2010

In Excel 2010 you can insert common mathematical equations into your worksheets or build your own equations with the new equation editing tools. Here’s how:
 

bullet

Click the Insert tab on the Ribbon

bullet

In the Symbols group, click the arrow next to Equation

bullet

Select from the equation gallery and the equation is inserted in a text box

To build your own equation:
 

bullet

Insert a text box (or shape)

bullet

Click to select the text box (or shape)

bullet

Click the Insert tab on the Ribbon

bullet

In the Symbols group, click Equation

bullet

The Equation Tools Design Ribbon is displayed

 
 

Calculate the Days, Months or Years between Dates in Excel

Use the DatedIf function to calculate the interval between dates in Excel. Here's how:

bullet

Enter the function into a cell

=DATEDIF ( start_date , end_date, unit )

bullet

For example, to calculate the number of months between two dates, if the start date is in cell D2 and the end date is in cell E2 you could enter this formula into cell F2:

To learn more about this function, go to to the Microsoft website or watch a YouTube video. This tip works in Excel 2007 and 2010, as well as earlier versions.
 

Using IF Functions in Excel 2007 and 2010

In a previous tip Using IF Functions in Excel we covered the basic If function which can be used in all versions of Excel. About.com explains the use of the If function and provides step-by-step tutorials on how to enter this function in 2007 and 2010:

bullet Using the Excel 2007 IF Function
bullet Using If Functions in Excel 2010

Download the If Function Worksheet and learn about the more complex uses of If functions.
 

Paste with Live Preview in Excel 2010

One of the improvements in Excel 2010 is the ability to preview what you are pasting before you paste it.  Here's how:

bullet Copy what you want to paste
bullet Position your pointer where you want to begin pasting
bullet Right click to display the shortcut menu which lists the most common choices

bullet Pause your pointer over the Paste Options buttons to preview the result before pasting
bullet Click to select your desired option or click the arrow beside Paste Special for more choices

For more help, watch this short video by Microsoft Paste with Live Preview.
 

Quick Navigation in Excel

To quickly move to the left, right, top, or bottom cell in a range of data, position the cursor at the beginning of the range and press:

 Ctrl + a cursor arrow

To select a range of data, position the cursor at the beginning of the range and press:

Shift + Ctrl + a cursor arrow
 

Using VLOOKUP in Excel

VLOOKUP is a powerful Excel function which allows you to look up a value in an Excel list or table.  For example, you could use this function to look up grades based on a percentage value: 

To learn more about this function, check out the free online training from Microsoft VLOOKUP: What it is, and when to use it.
 

Using Excel 2010 Sparklines

A new feature in Excel 2010 called Sparklines can help you spot trends in your data. Sparklines are tiny charts that sit on top of a cell. Here’s how to create them:

bullet Select an empty cell or group of empty cells where you want to insert Sparklines
bullet Click the Insert tab on the Ribbon
bullet In the Sparklines group, click the type of Sparkline you want to create
bullet In the Data box, enter the range of cells that contain the data to base the Sparklines on


 

Format Cells to Display the Cent Sign in Excel 2007

bullet

Select the range of cells you want to format

bullet

Right click on the selected range and choose Format Cells from the shortcut menu

bullet

Make sure the Number tab is selected in the Format Cells Dialog Box

bullet

Under Category select Custom

bullet

In the Type field enter [<1].00˘;$0.00_˘
(You can copy this format directly from this web page. If you type it yourself you will need to hold down the ALT key and enter 155 (or 0162) on the ten key pad to create the ˘ sign.)

bullet

Click OK
 

Insert a Worksheet Based on a Template in Excel 2007

bullet Right click a worksheet tab
bullet Select Insert
bullet Choose the template
bullet Click OK

You can choose a custom template which you have created, one of the templates available when you click the Spreadsheet Solutions tab, or click the Templates on Office Online button and choose from hundreds of templates available from Microsoft.
 

COUNTIFS in Excel 2007/2010

We've had the COUNTIF function in Excel for a while. This function allows you to count records in a list that match a single criteria. What's completely new with Excel 20072010 is COUNTIFS which allows you to count records based on multiple criteria.  Sure, there were other ways of doing this before, but none as easy as the COUNTIFS function.  Here's the syntax:

=COUNTIFS(range1,criterion1,range2,criterion2,…,range_n,criterion_n)

Makes it really simple to count records in a range that match the criteria you enter in the formula. If you need a little more help, check out this article on the Microsoft web site:  Use the COUNTIFS function in Excel 2007 to analyze data.
 

Gantt Charts in Excel 2007

A previously published tip Gantt Chart for Scheduling in Excel introduced Gantt charts as tools for project management and provided a link to a downloadable template.  Making a Gantt Chart in Excel 2007 is another excellent resource for creating Gantt Charts in Excel.  This 7-minute video gives you step-by-step instruction on how to use the built-in chart tool in Excel 2007 to create a Gantt chart.  If you need more help, check out this article by Michele McDonough Using Excel to Create a Gantt Chart.


 

AutoFill with Week Days

Hopefully you are already familiar with the Excel AutoFill feature for filling ranges with the months or days of the week.  But what if you need to fill an Excel range with just weekdays?  Here’s how:

bullet

Enter the starting day into a cell

bullet

Place the pointer over the lower right corner of the cell until you see the copy/fill handle (a thin black plus)

bullet

Right click the handle and drag to select the range you want to fill with weekdays

bullet

When you let up on the mouse button a menu will appear

bullet

Select Fill Weekdays

This tip works in Excel 2007 as well as earlier versions.
 

Long Text Entries in Excel 2007/2010

To break a long text entry into separate lines, position the insertion point in the cell entry or on the Formula bar where you want the new line to start and press Alt + Enter. Excel expands the row containing the cell when it starts a new line. Excel automatically wraps the text in the cell when you press Enter to complete the entry, according to the cell's column width and the position of the line break.  

Here is another method for dealing with long text entries in Excel 2007/2010:

bullet

Select the cell containing the long text entry

bullet

Click the Home tab on the Ribbon

bullet

In the Alignment group, click the Wrap Text button

Or you can right click the text entry and:

bullet

Select Format Cells from the shortcut menu

bullet

Click the Alignment tab

bullet

Click to select the Wrap text option
 

Cleaning your Data in Excel

If you have copied or imported data into your Excel workbook, you may need to clean it up.  An article from Microsoft entitled Top ten ways to clean your data covers features that will help you accomplish this.

The basics of cleaning your data include:

bullet

Removing duplicate rows

bullet

Finding and replacing text

bullet

Changing the case of text

bullet

Removing spaces and nonprinting characters from text

bullet

Fixing numbers and number signs

bullet

Fixing dates and times

bullet

Merging and splitting columns

bullet

Transforming and rearranging columns and rows

bullet Reconciling table data by joining or matching
 

Adding Paste Values to the Quick Access Toolbar in Excel 2007

Using the Paste Values option in Excel lets you strip formulas from your data and paste only the resulting values. In Excel 2007 the Quick Access Toolbar is a great way to simplify using this feature.  Here's how:

bullet

Click the Office button in the upper left corner of the Excel 2007 Screen

bullet

Click the Excel Options button at the bottom of the Office window to display the Excel Options dialog box

bullet

Click Customize in the left pane

bullet

Under Choose commands select All Commands

bullet

Select Paste Values

bullet

Click Add and then OK
 

Using the Excel 2007 Camera Tool

The Excel 2007 Camera tool lets you take a picture of a range of cells on a worksheet.  Before you can use this tool you must first add it to the Quick Access Toolbar on the Ribbon.

To add the Camera tool to the Quick Access Toolbar:

bullet

Click the Office button in the upper-left corner of the Ribbon

bullet

Click the Excel Options button

bullet

Click Customize

bullet

In the Choose Commands From drop-down list, select Commands Not in the Ribbon

bullet

Select Camera and double-click to add it to the Quick Access Toolbar

bullet

Click OK to close the Excel Options dialog box

To use this tool:

bullet

Select a range on your worksheet

bullet

Click the Camera tool on the Ribbon

bullet

Click where you want the picture to appear (In this workbook or even in another workbook)

A graphic is created of the range you selected.  If you change the original data the picture also changes.  You can even copy or move this picture to the clipboard and paste it into Word or PowerPoint if you need to. However, if you copy it into another program it will no longer update when the original is changed.
 

Change the Number of Default Sheets in an Excel 2007 Workbook

bullet

Click the Office button on the Ribbon

bullet

Click the Excel Options button

bullet

On the Popular tab, under When creating new workbooks, enter the number of sheets you want after the Include this many sheets option
 

Clear All Formatting in Excel 2007

bullet

Select the cell or range of cells to clear (or press Ctrl + A to select all)

bullet

Click the Home tab on the Ribbon

bullet

In the Editing group, click the Clear button

bullet

Select Clear Formats

Status Bar Sums

bullet

Select the cells you want to sum

bullet

Check the Status Bar in the lower right

All versions will display a Sum. By default, in Excel 2007 and 2010, the Count, Average and Sum will be displayed.  But this default can be changed by right clicking on the Status Bar to display the Customize Status Bar menu.
 

Tracking Revisions in Excel 2007

Excel 2007 tracks worksheet revisions so you can see what has changed on each sheet. To use this feature, do the following:

  1. Click the sheet to make it active
  2. Click the Review tab on the Ribbon
  3. Click Track Changes in the Changes group
  4. Choose Highlight Changes
  5. Click in the "Track changes while editing" box and then set the types of changes you want to highlight.

You can choose to have the changes highlighted on screen or listed on a new sheet.
 

Data Validation in Excel 2007

Data Validation lets you restrict what goes into a cell.  For example, if dates being entered must be between a certain range, do this:

bullet

Select the range of cells that you want to apply data validation to

bullet

Click the Data tab on the Ribbon

bullet

In the Data Tools group click the Data Validation button

bullet

Under Validation criteria choose Date from the Allow drop-down menu

bullet

Enter the acceptable date range

bullet

Click OK

If someone tries to enter a date outside this range a warning is displayed that says, "The value you entered is not valid.”
 

Import a Custom Auto Fill List in Excel 2007

bullet Select the range of cells on the worksheet which contains the list
bullet Click the Microsoft Office button
bullet Click the Excel Options button to display the Excel Options dialog box
bullet Click the Popular tab in the left pane
bullet Click the Edit Custom List button in the right pane to open the Custom List dialog box
bullet The range of selected cells will be displayed in the Import list from cells box at the bottom
bullet Click the Import button
bullet Your list now appears in the Custom Lists window
bullet Click OK
 

Change Text Orientation in Excel 2007

bullet Select the text you want to rotate, angle, or switch to vertical
bullet On the Home tab of the Ribbon, in the Alignment group, click the Orientation button
 

Change the Case of Text in Excel

To change the case of text in Excel, use one of the following formulas:

bullet

=UPPER(A1) Changes the text in cell A1 to upper case

bullet

=LOWER(A1) Changes the text in cell A1 to lower case

bullet

=PROPER(A1) Changes the text in cell A1 to proper case

 For example:


 

Display Formulas in Excel 2007 Worksheet

When creating Microsoft Excel worksheets formula results are shown in the worksheet NOT the formula. There may be times when it would be helpful if you could SEE the formula in the worksheet.

To display formulas in Excel 2007:

  1. Click the Office button

  2. Click the Excel Options button

  3. In the Excel Options dialog box, click Advanced

  4. Scroll down to the Display options for this worksheet section

  5. Click to select the option Show formulas in cells instead of their calculated values

To resume seeing the results of the formula, just repeat the above steps.  Or you can use the shortcut keys for toggling formula viewing on and off: Ctrl+` (accent key below the tilde ~).  This shortcut works in earlier versions of Excel as well.
 

Microsoft Excel 2007 Online Tutorials

If you’re trying to learn Microsoft Excel 2007 there are excellent online resources available. I recommend checking these out:

bullet

Microsoft Office Online offers several Excel 2007 Courses

bullet

Excel 2007 Tutorial developed by Florida Gulf Coast University
 

Worksheet Selection in Excel

If you have several worksheets in an Excel workbook, it can be time consuming to select the one you want to view. This shortcut can help you speed up the process.

bullet

Right click on the sheet tab navigation arrows

bullet

A menu of worksheet tabs pops up

bullet

Left click the worksheet you want to view

This tip works in Excel 2007 as well as earlier versions.
 

Shade Alternate Rows in Excel 2007

One way to quickly add shading to alternate rows in Excel 2007 is by applying a predefined table style.  By default, shading  is applied to alternate rows in an Excel 2007 table to make the data easier to read. The alternate row shading will remain accurate even if you add or delete rows.

bullet

Select the range of cells you want to format

bullet

On the Home tab, in the Styles group, click Format as Table

bullet

Under Light, Medium, or Dark, click the table style you want to use

bullet

In the Format as Table dialog box, click OK.  Notice that the Banded Rows check box is selected by default in the Table Style Options group.  To apply shading to alternate columns instead of alternate rows, clear this check box and select Banded Columns

bullet

If you want to convert the Excel table back to a regular range of cells, click anywhere in the table to display the tools necessary for converting the table back to a range of data

bullet

On the Design tab, in the Tools group, click Convert to Range
 

Flipping Data in an Excel 2007 Worksheet

Ever create an Excel 2007 table and then wish the columns were rows and the rows were columns? Here's a solution:

bullet

Select the table

bullet

Press Ctrl + c to copy (Or click the copy button on the Home tab of the Ribbon)

bullet

Select the cell where you want the new table to begin (this cell CAN be in the old table)

bullet

Right click to display the shortcut menu and select Paste Special (Or on the Home tab of the Ribbon, click the Paste arrow to display the Paste Special option)

bullet

In the Paste Special dialog box, select Transpose and click OK
 

Use Text-to-Speech in Excel 2007/2010

Text-to-speech was not included in the Excel 2007 Ribbon. To use this feature in Excel 2007/2010 you must first add it to the Quick Access Toolbar.  Here’s how:

bullet

Click the Customize Quick Access Toolbar arrow

bullet

Click More Commands from the drop-down menu

bullet

From the Choose commands from list, select Commands Not in the Ribbon

bullet

Scroll down and select the Speak Cells commands you want to use and click Add

bullet

Click OK when you are finished adding commands to your Quick Access Toolbar

Now you can select a group of cells to read back, click the speak button, and Excel will read your data.  Of course, you need speakers or a headset to hear it!  For more information on using this feature go to Converting text to speech in Excel.
 

Using Paste Link in Excel 2007

Most people know how to Cut, Copy and Paste in Excel 2007 but many don’t use Paste Link, which creates a link between the data or formula in the original cell and the destination cell where it’s pasted.  Here’s how:

bullet

Click on a cell to make it the active cell

bullet

Click on the Copy button on the Home tab (or press Ctrl + c)

bullet

Click on the destination cell where you want the results to be displayed

bullet

Click the small arrow at the bottom of the Paste button to open the  drop-down list

bullet

Click Paste Link

The contents of the original cell now also appear in the destination cell.
 

Workbook Capacities in Excel 2007

If you've upgraded to Excel 2007, or if you are thinking about it, you might be interested in the increased workbook capacities.  With Excel 2007 you are no longer limited to 3 sort levels and a worksheet can contain more than a million rows.

  Workbook Capacities

Excel 2003

Excel 2007

Number of Worksheets 255 Limited to memory
Columns 256 16,384
Rows 65,536 1,048,576
Colors 56 16 million
Sort levels 3 64
Characters in formula 1,024 8,192
Nested levels in formulas 7 64
Arguments in a function 30 255
Conditional formats 3 Limited to memory


Find Your Excel 2007 Serial Number

When you first installed Excel 2007 you had to enter a product key number which was probably located on the CD. This ID is your serial number. You will need to have this number If you ever try to get technical support from Microsoft.  Here’s how you can find it:

bullet

Click the Office button on the Ribbon

bullet

Click the Excel Options button

bullet

Click Resources

bullet

Click the About button to display the About Microsoft Excel dialog box

bullet

Your product ID code is displayed on this dialog box
 

Sorting and Filtering in Excel 2007

A big improvement in Excel 2007 is the ability to sort on up to 64 levels instead of the 3 we had  available in prior versions. You can also filter data by color or by dates and Excel will display more than 1000 items in the AutoFilter drop-down list.  A quick way to sort or filter data in Excel 2007 is to use the shortcut menu:

bullet

Right click a column in your table

bullet

Select Filter or Sort from the shortcut menu

bullet

To sort by the column selected, click Sort A to Z or Sort Z to A

bullet

To sort on multiple criteria, select Custom Sort to display the Sort dialog box
 

Add Color to Worksheet Tabs in Excel 2007

 It can be useful to color code the tabs of Excel worksheets:

bullet

Select the tab of the sheet you want to re-color (to select more than one tab hold down the CTRL key and click each tab)

bullet

Right click and select Tab Color from the shortcut menu

bullet

Select color and click OK

This tip also works in previous versions of Excel.
 

Creating a Template in Excel 2007

Templates are boilerplate worksheets that can save time and promote standardization. To create an Excel 2007 template:

bullet Enter all necessary data and apply required formatting
bullet Click on the office button to open the  drop-down menu
bullet Choose the Save As option
bullet Choose the Other formats option
bullet Choose the Save As option to open the Save As dialog box
bullet Click on the Save as type option to open the  drop-down list
bullet Scroll through the list to find the template options
bullet For most templates, choose the Excel Template (*.xltx) option
bullet If your template contains macros, choose the Macro Enabled Template (*.xltm) option
bullet If you plan to use your template with older versions of Excel, choose the Excel 97 - 2003 Template (*.xlt) option

To use your template to create a new worksheet, click the office button and select New.  Your template will be listed in the My templates… folder.

On the Web you can find thousands of Excel templates for just about anything you can think of.  A good place to start is office.microsoft.com.
 

Protect Cells in Excel 2007/2010

You can prevent users from overwriting formulas or inadvertently changing labels or formats by protecting cells in a worksheet. When you protect a worksheet all cells are protected, or locked, and cannot be changed. To allow users to change certain cells in the worksheet you first identify which cells should NOT be locked. Then you protect, or lock, the worksheet. In Excel 2007, follow these steps:

Unlock cells that can be changed

  1. Select the cells that users should be able to change
  2. Click the Home tab on the Ribbon
  3. In the Font group, click the small arrow in the lower right corner to open the Format Cells dialog box
  4. Click the Protection tab
  5. Deselect the Locked checkbox and click OK
  6. Repeat these steps for each range of cells that can be changed, then protect the worksheet

Protect the worksheet

  1. Click the Review tab on the Ribbon
  2. In the Changes group, click Protect Sheet and click OK
  3. In the Protect Sheet dialog box enter a password, or leave blank if you don’t want a password, and click OK

Now users can only change the cells that are unlocked. If users try to change any other cells they receive a message saying they must unprotect the cells to modify them.
 

Total Data in an Excel 2007 Table

New in Excel 2007, you can now total the data in a Microsoft Office Excel table by displaying a totals row at the end of the table. Here's how:

bullet

Click anywhere in the table to display the Table Tools Design tab

bullet

Click the Design tab

bullet

In the Table Style Options group, select the Total Row check box

bullet

In the total row, click the cell in the column you want to calculate a total for

bullet

Click the drop-down list arrow that appears

bullet In the drop-down list, select the function you want to use to calculate the total
 

Hiding Columns or Rows in Excel 2007

Here's how to hide columns or rows in your Excel 2007 worksheet:

  1. Select the rows or columns you want to hide
  2. Click the Home tab on the Ribbon
  3. In the Cells group, click Format
  4. From the drop-down menu under Visibility point to Hide & Unhide
  5. Select Hide Rows or Hide Columns

To unhide columns or rows:

  1. Select the rows above and below the rows you want to unhide or select the adjacent columns on either side of the columns you want to unhide
  2. Click the Home tab on the Ribbon
  3. In the Cells group, click Format
  4. From the drop-down menu under Visibility point to Hide & Unhide
  5. Select Unhide Rows or Unhide Columns
     

Working with Large Excel 2007 Worksheets

Worksheets that are too large to be displayed on a single screen can cause problems when entering data as well as printing. There are 2 different features that will help you deal successfully with these issues.

To enter data into a worksheet that is wider or longer than the available screen area:

bullet Position the pointer beside and below the rows and/or columns to keep on the screen
bullet Click the View tab on the Ribbon
bullet In the Window group click Freeze Panes

To print a worksheet that is wider or longer than the available page size without losing the rows and/or columns required to give meaning to your data:

bullet Click the Page Layout tab on the Ribbon
bullet In the Page Setup group click Print Titles to display the Page Setup Dialog box
bullet Click the Sheet tab
bullet Select the columns and/or rows that need to be repeated on each printed page
bullet Click OK
 

Calculated Columns in Excel 2007 Tables

bullet Click a cell in a blank table column that you want to turn into a calculated column
bullet Type the formula
bullet The formula is automatically filled into all cells of the column
 

Freeze a Formula into its Current Value

To freeze a formula into its current value:

bullet

Select the formula

bullet

Press F2 (Edit)

bullet

Press F9 (Calc)

bullet

Press Enter

Now you can copy or move the value anywhere you need it. This trick works in all versions of Excel, including 2007 and 2010!
 

Adding Data Forms to the Excel 2007 Quick Access Toolbar

Those of you that enjoyed using the feature called Data Form in earlier versions of Excel may be unhappy when you can’t find it anywhere in the new 2007 Ribbon interface.  But you CAN add it to the Quick Access Menu at the top of the Excel 2007 screen, and here’s how:

bullet

Click the Office button in the upper left corner of the Excel 2007 Screen

bullet

Click the Excel Options button at the bottom of the Office window to display the Excel Options dialog box

bullet

Click Customize from the Options list

bullet

Select Commands Not in the Ribbon from the Choose commands from drop-down list

bullet

Select Form

bullet

Click Add and then OK
 

Keyboard Shortcuts in Excel 2007/2010

If you are a fan of keyboard shortcuts you will be happy to know that most of the shortcuts we've used for years work exactly the same in Excel 2007/2010.  Here's a list of some of my favorites:

Action Keys
Start a new line in the same cell Alt + Enter
Selects the entire worksheet or the current range Ctrl + A
Undoes the last action Ctrl + Z
Redoes the last action Ctrl + Y
Calculates all worksheets in all open workbooks F9
Copy selected cells Ctrl + C
Paste Ctrl + V

For a complete listing of all Excel 2007 keyboard shortcuts, go to Excel shortcut and function keys published on www.office.microsoft.com where you can find lots of other great resources for learning Microsoft Office.
 

Remove Duplicate Records in Excel 2007 Database

bullet

Click in the database

bullet

Click the Design tab

bullet

In the Tools group click Remove Duplicates
 

Text to Columns in Excel 2007

This feature can be useful for creating a database using existing information which is improperly formatted. For example, if both first and last names have already been entered into a cell, use text to columns to split names into 2 columns.

bullet Select the range of cells that contains the text values
bullet Click the Data tab
bullet In the Data Tools group, click Text to Columns
bullet Follow the instructions in the Convert Text to Columns Wizard

Click here if you're using an earlier version of Excel
 

Changing the Color of Gridlines in Excel

Did you know you can change the color of the gridlines in an Excel worksheet? Here’s how:

bullet Choose Tools from the pull-down menu
bullet Select Options
bullet From the View tab be sure the Gridlines check box is selected
bullet Choose a color from the Gridlines color drop-down menu
bullet Click OK

Since the gridlines do NOT print this is only for your viewing pleasure.
 

Selecting a Range in Excel

In Excel, a range is a group of connected cells. When working in Excel you often need to select a range.  Here is a simple trick for quickly selecting a range:

bullet Click anywhere within the range
bullet Press Ctrl + Shift + *

The range is selected.  This trick works in all versions of Excel.
 

Generating Random Numbers in Excel

Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically:

bullet

Type =RAND() in a cell to generate a number between 0 and 1

bullet

Type =RAND()*100 to generate a number between 1 and 100
 

Display Fractions in Excel Worksheet

Have you ever wanted to display a fraction in an Excel worksheet?  Try this:

bullet Before typing fractions in cells, pre-format the cells by selecting the cells where you want to enter fractions and choosing Format from the pull-down menu
bullet Choose cells from the Format Cell dialog box
bullet Select fraction from the category list
bullet Select the type of fraction that you would like to display and click OK
bullet Type the fraction using a forward slash between the numerator and denominator – do not type spaces – for example, type 1/3 to represent one-third
bullet If you need to type a whole number and a fraction type a space between the whole number and fraction, for example 1 1/3
 

Combining Text from Two Cells in Excel

In Microsoft Excel you can combine the text in two or more cells into a single text string by using “concatenation”.  The result is displayed in the cell where you type the formula.

For example, If you have a worksheet that contains the First name in cell A1 and the Last name in cell B1, enter the following expression in cell C1 to create a text string that displays the values of the First and Last names separated by a space:  =A1&" "&B1

Be sure to put a space between the double quotes in the expression. Copy this formula down to each row containing the names. Then copy the cells containing this formula back to their original position using Paste Special to convert the formula into a value:

bullet Select the column by clicking the column name
bullet Choose Copy
bullet Right click and choose Paste Special from the short-cut menu
bullet Choose Values and click OK

This tip works in Excel 2007 and 2010 as well as earlier versions.

Calculate Remaining Days in the Year

Have you ever wanted to calculate the number of days remaining in the year? If the date is in cell A1, use the following formula:

	=DATE(YEAR(A1),12,31)-A1
 

Protect Cells in Excel

You can prevent users from overwriting formulas or inadvertently changing labels or formats by protecting cells. Follow these steps:

  1. Select the cells that users can change during data entry

  2. Choose Format from the pull-down menu

  3. Choose Cells

  4. Deselect the Locked checkbox on the Protection tab, and click OK

  5. Choose Tools from the pull-down menu

  6. Choose Protection

  7. Choose Protect Sheet, and click OK

Now users will be able to alter only the cells that are unlocked. If users attempt to alter any other cells they will receive a message saying they must unprotect the cells to modify them. In Step 7, you may wish to add a password to prevent unauthorized users from changing a cell's protection level.
 

Calculated Fields in Excel Data Forms

In a previous Tip we learned an easy way to enter information into an Excel worksheet using "form view".  But what about calculated fields? For example, let’s say your worksheet includes three columns; Quantity, Price, and Total which is a calculated field.

 If you would like to have the calculated field entered as you type, try this technique:

bullet Enter the column headings
bullet Enter the formula for the calculated field
bullet Click any of the column labels
bullet Select Data from the pull-down menu
bullet Choose Form

 As you add records, Excel will copy the formula in each record. (You will not see the value in "form view" unless you navigate between records using the form’s Find Prev or Find Next Buttons.)
 

Text to Columns in Excel

The Text to Columns feature in Excel is used to split text entered in a single cell into columns. For example, if both first and last names have already been entered into a cell, use text to columns to split the names into 2 columns.

bullet

Select Text

bullet

Choose Data from the pull-down menu

bullet

Choose Text to Columns to display the Convert Text to Columns Wizard, which will guide you through the simple 3-step process
 

Gantt Chart for Scheduling in Excel

A Gantt chart is a common tool for project planning and keeping track of the status of individual tasks within a project.  Go to www.vertex42.com to download a free Gantt chart spreadsheet for creating simple project schedules and timelines.

 
 

Add Color to Worksheet Tabs in Excel

 It can be useful to color code the tabs of Excel worksheets:

  1. Select the tab of the sheet you want to re-color. To select more than one tab hold down the CTRL key and click each tab.

  2. Choose Format from the pull-down menu

  3. Select Sheet

  4. Select Tab Color

  5. Select a color from the Format Tab Color dialog box

  6. Click OK
     

Add Text to Displayed Numerical Values in Excel

You can add text to the value of a cell by following these steps:

  1. Choose Format from the pull-down menu
  2. Select Cells
  3. Select Custom from the Category list
  4. In the Type box, select the default value General
  5. After the word General, enter a space and a quote, next enter the word you want to display and another quote. For example General "tons"
  6. Click on OK

The format you create will put the word tons after any number you enter into the cell, but Excel will still treat the value as a number, not text
 

Counting Data in Excel

In the article "Count and Sum Your Data in Excel 2002" by John Walkenbach Microsoft MVP, you can learn various ways to count and sum records based on criteria.  For example: to count records for Smith, if A1:A125 is the column containing the names, use this formula:

=COUNTIF(A1:A125,"Smith")

To read the entire article, go to: www.microsoft.com
 

Shade Alternate Rows in Excel

Shading alternate rows can improve legibility in Excel worksheets containing long lists.  Conditional Formatting can be used to apply cell shading to every other row in a worksheet range.

  1. Highlight the range of cells that you want to format
  2. Choose Format from the pull-down menu
  3. Select Conditional Formatting to display the Conditional Formatting dialog box
  4. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box
  5. Click the Format button to bring up the Format Cells dialog box
  6. Select the Patterns tab and specify a color for the shaded rows.  Choose a light color so the black text will still be legible
  7. Click OK twice to return to your worksheet
     

Disable Drag and Drop in Excel

If you place your mouse pointer on the side of a cell in an Excel worksheet, it changes from an arrow or plus sign into a four-sided arrow. If you click and hold the left mouse button down you can drag and move the cell information. It's easy to move data you don’t want to move. To turn this feature off:

  1. Click Tools from the pull-down menu
  2. Choose Options
  3. Click the Edit tab
  4. Uncheck "Allow cell drag and drop"
  5. Click OK
     

Display Formulas in Excel Worksheet

When creating Microsoft Excel worksheets formula results are shown in the worksheet NOT the formula. There may be times when it would be helpful if you could SEE the formula in the worksheet.

To display formulas:

  1. Select Tools from the pull-down menu

  2. Choose Formula Auditing

  3. Choose Formula Auditing Mode

To resume seeing the results of the formula, just repeat the above steps.  Or you can use the shortcut keys for toggling formula auditing mode on and off: Ctrl+`.
 

Calculate a Person's Age in Excel

The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. This function makes it easy to calculate a person's age. To try this:

  1. In a blank worksheet, type a birth date in cell A1, using slashes to separate day, month, and year.
  2. In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.

The age (in years) will be displayed in cell A2.
 

Close all Open Files in Excel or Word

bullet Hold down the shift key
bullet Select "File" from the pull-down menu
bullet Click "Close All"
 

Simplify Data Entry with “Form View"

Ever tried entering data into a wide Excel worksheet? When columns expand past your viewing area it can become difficult. To enter data or search for data without having to do a lot of scrolling, use "form view".

  1. Open your worksheet and select any cell within the worksheet range
  2. Click Data on the menu bar
  3. Click Form on the drop-down menu
  4. Click the New button to enter a new record; Excel displays a blank form
  5. Enter data into the first field and then press the Tab key to move to the next field
  6. When you get to the end of your data entry, press the Enter key to go to the next blank record

Note: If there are formulas in your worksheet, the "form view" displays only the results of the formula, thus preventing it from being accidentally overwritten.

To search for specific data or a record, do the following:

  1. Click the Criteria button to clear the form
  2. Enter your search term into the appropriate field (e.g., enter Human Resources into the "Department Name" field)
  3. Click the Find Next button to find the next instance, or click the Find Previous button to find the previous instance

Note: If you enter multiple criteria, Excel will find the record that meets all of the criteria. The "form view" does not support OR searches. You can also use the scrollbar to scroll through the records.
 

Using IF Functions in Excel

The Excel IF function is a great tool that can be used when the information you want in a cell is conditional. It’s particularly handy if you need to specify two or more different responses for a cell based on specified conditions.  The format for this function is:

=IF("if the condition stated here is true", "then enter this value", "else enter this value")

For example, to see at who passed or failed an exam an IF function could be used. Let’s assume the pass mark is 75 and the marks are in column B, starting from row 5. the formula would look like this:

=IF(B5>75, "Pass", "Fail")
 

Drop-Down Lists Increase Data Accuracy

Giving users the option to select data from lists rather than type it manually can help eliminate many data entry errors. But you don't need to create the actual lists to ensure accuracy.  After users enter an item once, Excel automatically includes it in a drop-down list of entries that users previously typed in the column. 

For example, to access the list of vendors previously typed in the Vendors column of an Orders spreadsheet, follow these steps:

  1. Select the cell under the Vendors column for the next order.

  2. Press [Alt] and the down arrow.

  3. Select the vendor's name from the list, and press [Enter] or [Tab] to move to the next cell. If the vendor's name is not on the list, users must type it manually. Excel will include the name the next time someone accesses the list.

Of course, if the automatic completion feature is turned on, data will be entered automatically when you begin typing a column entry for the second time!  To accept the proposed entry, press TAB or ENTER. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries.

To turn automatic completion of cell entries on or off:

  1. On the Tools menu, click Options, and then click the Edit tab.

  2. Select or clear the Enable AutoComplete for cell values check box.
     

Using Text to Speech in Excel

Proofreading a spreadsheet can be time-consuming. The Text to Speech feature in Excel 2002 can really help speed up this process by reading selected data back to you for verification. Each cell is highlighted as the value is spoken, and when you hear an error, you can stop to correct the error in that cell.

To play back a group of cells:

  1. On the Tools menu, point to Speech, and then click Show Text To Speech Toolbar.

  2. Select a group of cells to read back.

  3. Choose how the computer will read back your data by clicking By Rows or By Columns on the Text To Speech toolbar.

  4. Click Speak Cells if you want the computer to read back each cell in your selection.

  5. To correct an error, click Stop Speaking, and use your mouse and keyboard to make the necessary changes.

  6. Click Speak Cells to continue.

To play back after every cell entry:

  1. On the Text to Speech toolbar, click Speak On Enter.

  2. Enter data in a cell. After you press ENTER, the computer will read back the data in the cell.

Note: To use Text to Speech, your computer must have a sound card installed and speakers attached. The available voices depend on your default language installation and any language packs you may have installed. Text to Speech is not part of the standard installation of Excel, so have your installation CDs ready.
 

Define Constant Values in Excel

Use the Name tool to define a constant value, such as a tax rate, that you frequently use in Excel formulas. Go to Insert | Name | Define and type a name—TaxRate, for example. In the Refers to area, type the constant value and click on OK. For a 9 percent tax rate, you would enter 0.09. For example, enter the formula =5000*TaxRate and Excel will use the constant value defined for TaxRate and return 450.
 

Microsoft Excel Online Tutorial

If you’re trying to learn to use Microsoft Excel, try this online tutorial developed by Florida Gulf Coast University:

Spreadsheet Basics  Sorting and Filling
Customizing Excel Graphics
Modifying A Worksheet Charts
Formatting Cells Page Properties and Printing
Formulas and Functions Keyboard Shortcuts


Enter a Line Break Within a Cell in Excel

To Enter line breaks in an Microsoft Excel worksheet:

  1. Click the cell where you want the label or heading to appear
  2. Type the first line of information
  3. Press ALT + ENTER
  4. Type the second line (Repeat step 3 if you have additional lines to enter)
  5. Press ENTER when you've finished typing
     

Long Text Entries in Excel

To break a long text entry into separate lines, position the insertion point in the cell entry or on the Formula bar where you want the new line to start and press Alt+Enter. Excel expands the row containing the cell when it starts a new line. Excel automatically wraps the text in the cell when you press Enter to complete the entry, according to the cell's column width and the position of the line break.  

Here is another method for dealing with long text entries:

  1. Select the cell containing the long text entry
  2. Choose Format and Cells from the pull-down menu
  3. Click the Alignment Tab
  4. Select Wrap text
     

Linking Cells to a Textbox or Object in Microsoft Excel

  1. From the Drawing toolbar, select Text Box. Add a text box to the worksheet.

  2. Select the Text Box, and press F2.

  3. In the Formula bar, create a link to a cell by typing = and then selecting the cell.

  4. The contents of the cell are displayed in the Text Box. For Example:


 

Insert Time/Date in Excel or Access

Here are a few keyboard shortcuts you can use to insert the current time and date in a Microsoft Access table or Microsoft Excel spreadsheet.

bullet

Current date: Press CTRL+SEMICOLON

bullet

Current time: Press CTRL+SHIFT+ SEMICOLON

bullet

Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON

In Access, this keyboard shortcut only works if you are entering data in the Datasheet or Form view.

Editor's Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions. To learn how to do this, search for Insert the current date and time in a cell in Excel Help and then click Insert a date or time whose value is updated.

This tip comes to us from www.microsoft.com, courtesy of Kimberly Schenk, Smyrna, Tennesse.
 

Working with Large Excel Worksheets

Worksheets that are too large to be displayed on a single screen can cause problems when entering data as well as printing.  There are 2 different features that will help you deal successfully with these issues.

To enter data into a worksheet that is wider or longer than the available screen area:

  1. Position the pointer beside and below the row and/or column names. 
  2. Select Window from the pull-down menu. 
  3. Click Freeze Pane.

To print a worksheet that is wider or longer than the available page size without losing the rows and/or columns required to give meaning to your data:

  1. Choose File from the pull-down menu. 
  2. Select Page Setup and click the Sheet tab. 
  3. Select the appropriate Print Titles option and click the columns and/or rows that need to be repeated on each printed page.
     

Flipping Data in an Excel Worksheet

Ever create an Excel table and then wish the columns were rows and the rows were columns? Here's a solution:

  1. Select the table.
  2. Choose Edit + Copy.
  3. Select the cell where you want the new table to begin (this cell CAN be in the old table).
  4. Choose Edit + Paste Special.
  5. In the Paste Special dialog box, select Transpose and click OK.
  6. If necessary, delete the remains of your old table.
     

Displaying Chart Tips in Excel

If you have tried to display Chart Tips but couldn't get the tips to display, try this:

  1. Choose Tools + Options.
  2. In the Options dialog box, click the Chart tab.
  3. Under Chart Tips, select both Show Names and Show Values.
  4. Click OK.
     

Shortcuts for Using Names in Formulas

Next time you'd like to add a name to a formula you're writing:

  1. With the cursor positioned where you want to insert the name, press F3. The Paste Name dialog box appears.
  2. Using your up- and down-arrow keys, select the name that you want to insert (you can use the mouse, too, but why take your hands off the keyboard?).
  3. Press Enter.     
     

Quickly Sum Any Row or Column of Data

  1. Select the cell just beneath the column or just to the right of the row.
  2. Click the AutoSum icon (on the Standard toolbar).

What you may not know is that you can get the same job done without so much as a glance at the mouse:

  1. Select the cell just beneath the column or just to the right of the row.
  2. Press Alt + = and press Enter.
     

Changing Color to Black and White

You've formatted your cells in brilliant color. But your printer is black and white. So on paper, your brilliant colors are translated into bland shades of gray--gray that sometimes is dark enough to make your cell contents hard to read.

Guess the only thing to do is reformat all those colored cells to light gray or no color at all, right? WRONG.

Anticipating a legion of black-and-white printer users who couldn't resist formatting in color, the makers of Excel have included a neat feature that converts your colors to sensible, legible grays:

  1. Choose File + Page Setup.
  2. Click the Sheet tab.
  3. Under Print, select Black and White.
  4. Click OK.
  5. Print as usual.

One warning: Like many of Excel's other color printing features, this one does NOT seem to work on an HP LaserJet 4 printer. Wish we knew a reason why.
 

Turn a Date into a Weekday

  1. Select the cell containing the date.
  2. Right-click the cell and choose Format Cells from the shortcut menu.
  3. Click the Number tab.
  4. Under Category, select Custom and, in the Type box, type "dddd" (without the quotation marks).
  5. Click OK.
     

Create Instant Range Names

You've got a huge table, and you want to turn all the row and column labels into names. Big job, right? Wrong again, financial-statement breath. If your cursor is ANYWHERE WITHIN THE TABLE, you can get the whole job done with three keystrokes:

  1. Press Ctrl + Shift + *. This keystroke selects the entire current table--that is, the current region enclosed by empty rows and columns. (If your table ISN'T enclosed by blank rows and columns OR if your table contains blank rows and columns, you have to select the table manually.)
  2. Press Ctrl + Shift + F3. This keystroke displays the Create Names dialog box, with Top Row and Left Column already selected!
  3. Press Enter. This keystroke is the same as clicking OK.

Your names have been created.
 

Using Names in Formulas

If you've got names in your worksheet and you want to write formulas that refer to these names, try this:

  1. Start typing your formula.
  2. When you get to where you want to insert a name, press F3.
  3. In the Paste Name dialog box, select the name you want to insert into your formula.
  4. Click OK.
  5. Continue entering your formula, repeating Steps 2 through 4 if you need to insert another name.

Not only have you spared yourself the digital (in both senses of the word) pain of typing the names, but you've also eliminated the possibility of misspelling the name.
 

Help Entering Formulas

If you want help entering a formula --one that includes a function, that is--try this:

  1. Type the name of the function, such as "=SUM" (without the quotation marks)
  2. Press Ctrl + Shift + A

Excel displays the function's arguments RIGHT THERE IN THE CELL (and in the Formula bar); all you have to do is replace the function arguments with cell addresses.
 

Autofill

AutoFill the last day of each month--even though they're not the same number of days apart.

  1. Type the last day of a month--as in "6/30/99"--in one cell (without the quotation marks).
  2. Type the last day of the next month--"7/31/99"--into the cell below or to the left (again, without quotation marks).
  3. Select both cells.
  4. Using the AutoFill handle, drag to the left or down.

Excel understands that you want the last days of each month (including leap days, of course!).
 

Moving Around an Excel Worksheet

Today, we give you the handy Go To method. Suppose you want to select the range A1:Z52:

  1. Select the cell at the top-left corner of the range you want to select (in this case, cell A1).
  2. Choose Edit + Go To (OR press Ctrl + G).
  3. In the Reference box, type the address of the cell at the bottom-right corner of the range you want to select (in this case, Z52).
  4. Hold down the Shift key.
  5. Press Enter (or click OK).

The troublesome large range is selected without scrolling.

Copying Selected Characters

"If I have a column of text in which each cell contains eight characters, how might I create a second column in which each cell contains only the first five of these characters? Is this possible?"

Yes. Suppose the column containing the eight-character cells is column A (starting in cell A1), and you want the corresponding first-five-character entries in column B (starting in cell B1). Try this:

  1. In cell B1, type "=LEFT(A1,5)" (without the quotation marks).
  2. Press Enter. The LEFT function, as entered, copies from cell A1 the first five characters from the left.
  3. Choose Edit + Copy.
  4. Select the remaining cells in column B that you want to copy the formula into and press Enter.
     

Name a Number

You may be familiar with using Excel's Name feature. This feature lets you name a cell (or range of cells) so that you can refer to the cell (or range) by name rather than the more complex cell address in formulas.

But the Name feature has another use: You can use it to assign a name to a number that's NOT EVEN ENTERED IN YOUR WORKSHEET. Suppose, for example, that a particular worksheet requires you to regularly multiply numbers by the number .267. You can assign a name to the number .267--and use the name in your formulas--as follows:

  1. Choose Insert + Name + Define.
  2. Type a name for your multiplier--such as "profit" (without the quotation marks).
  3. In the Refers To box, type the value--in this case, ".267" (without the quotation marks).
  4. Click OK.

Now you can use this multiplier in a formula. For example, suppose you want to multiply a value in the cell A1 by this named constant:

  1. Select an empty cell.
  2. Type "=A1*profit" (without the quotation marks).
  3. Press Enter.

Excel multiplies the contents of A1 by .267.
 

Using Goal Seek

You build a sales forecast in Excel, and the totals look SO good you decide to chart them. Only problem is that once you chart them, you wish they looked a little better.

Our advice: Change the chart! Or, more precisely, use the chart to initiate a goal-seek operation, as follows:

  1. Select the chart element--the bar or line--that you wish were a bit larger.
  2. Drag the largest handle on the element to enlarge the element, making it about as large as you want it to be (don't worry about being precise just yet).
  3. In the Goal Seek dialog box--which appears as soon as you adjust the chart element--set the To Value field to the exact value you want for the adjusted element.
  4. In the By Changing text box, type the address of the cell you want to change to get the desired value. Obviously, this must be a cell referenced in the formula that generates the value of the bar you adjusted.
  5. Click OK.

Excel "raises the bar" on your chart and adjusts the selected cell accordingly.
 

Crowded Worksheets? Add These Buttons

One way to relieve worksheet crowding is to rotate your column labels so they read up, down, or vertically. Of course, because those rotation commands are buried deep in your Format Cells dialog box, they aren't particularly accessible.

Add them to the Format toolbar, as follows:

  1. Choose Tools + Customize.
  2. Click the Commands tab.
  3. Under Categories, choose Format.
  4. Under Commands, find the Vertical Text button and drag it into place on your Formatting toolbar.
  5. Repeat Step 4 to drag the Rotate Text Up, Rotate Text Down, and, if desired, Angle Text Upward and Angle Text Downward buttons to the Formatting toolbar.
  6. Click Close.

Now, whenever you want to angle or rotate text, just select the cell(s) and click the appropriate button
 

Justified Text

To look at Excel's Formatting toolbar, you'd think there is absolutely no way to justify (align) text in an Excel cell. But you most certainly can, like so:

  1. Select the cell(s) in which you want to align text.
  2. Right-click the selection and choose Format Cells from the shortcut menu.
  3. Click the Alignment tab.
  4. In the Horizontal box, select Justify.
  5. Click OK.

Because Excel has NO shortcut key for justifying text (such as Word's Ctrl + J shortcut key), you may want to move the Justify button to the Formatting toolbar as follows:

  1. Choose Tools + Customize.
  2. Click the Commands tab.
  3. Under Categories, choose Format.
  4. Under Commands, find the Justify button and drag it to your Formatting toolbar.
  5. Click Close.
     

Default Row Height and Column Width

Last time, we told you that Excel's default row height was 12.75 and that its default column width was 8.43. What we didn't define--and what the Row Height and Column Width dialog boxes do not make clear--is what the units of measurement are.

Assuming that you're losing sleep over this, we'll tell you: Excel's default row height is 12.75 points (a point is 1/72 of an inch). Excel's default column width is 8.43 characters.
 

Fitting Worksheets on Pages

In the past, we've told you how to use Excel's Fit To printing option to fit slightly more than a page on a page. But you can also use the option to fit a larger range onto a specified number of pages.

For example, to print a 10 column by 250 row worksheet on two pieces of paper, you'd do the following:

  1. Choose File + Page Setup.
  2. Under Scaling, set Fit To to 1 Pages Wide by 2 Pages Tall.
  3. Click Print.
  4. Click OK.
     

SmartFill

By now you all know about Excel's SmartFill feature, which lets you quickly fill a range with successive numbers, dates, and so on, simply by dragging two existing dates. But what you may not know is just how smart the feature is. For example, if you want to fill Row 1 with the last day of each month, just do as follows:

  1. In cell A1, type the last day of a month, as in "01/31/99" (without the quotation marks).
  2. In cell A2, type the last day of the next month (in this example, "02/28/99"--again, without the quotation marks).
  3. Select A1:A2.
  4. Using the fill handle in the bottom right corner of the selection, drag across Row 1.

Excel fills each cell you drag over with the last day of each successive month. You can even use the same technique to fill every other day, every third day, every 10 minutes, every three months, and so on. Go ahead, enjoy this new and exciting knowledge!
 

Tracking Revisions

Excel tracks worksheet revisions so you can have some idea of what has been done to each sheet. To see these tracks, do the following:

  1. Click the sheet to make it active.
  2. Choose Tools + Track Changes.
  3. Choose Highlight Changes.
  4. Click to check in the "Track changes while editing" box and then set the types of changes you want to highlight.

You can choose to have the changes highlighted on screen or listed on a new sheet.
 

Hiding Workbooks, Worksheets, Columns or Rows

You can hide some of the data on your worksheet. This isn't password security, but it does keep others from instantly seeing what you're working on.

  1. To hide an entire workbook, choose Window + Hide.
  2. To hide a worksheet, select it (click on its tab), choose Format + Sheet, and select Hide.
  3. To hide a row or column, click on it to select it, choose Format + Row or Column, and select Hide.
     

Data Validation

"Validation" lets you restrict what goes into a cell. Although you don't need this feature so much for your own work, it is a fine tool when you set up a worksheet that someone else will use. You create a validation test that makes sure only the right type of data--and only within the appropriate range--goes into a cell. For example, to create a cell that asks for a date, which must be within the next year, you'd do the following:

  1. Click the cell
  2. Choose Data + Validation.
  3. In the Data Validation dialog box, click the Allow drop-down menu
  4. Choose what kind of data you want for this cell
  5. In the additional menus that appear, set the limits for that data. For Date validation, for example, you specify which range of dates are acceptable
  6. Click OK

If someone tries to enter anything other than a date or a date outside the approved range into this particular cell, that person sees a warning that says, "The value you entered is not valid," and gets a chance to enter something else.
 

Working with Multiple Worksheets

Let's hope you never have to wrestle with complicated Excel documents. You know, the ones with lots and lots of worksheets. But if you do, it's nice to know that you can see what's in that document at a glance. To see a list of all that's in the document, do the following:

  1. Choose File + Properties
  2. In the Properties dialog box, click the Contents tab
     


"No sensible decision can be made any longer without taking into account not only the world as it is but also the world as it will be."  Isaac Asimov  View Past Quotes

frank@createthefuture.com susan@createthefuture.com

© 2014 Creative Information Systems 

 In Association with Amazon.com

Revised: April 21, 2014