Change The Color Of Worksheet Tabs

Category: General | [Item URL]

Q. Can I change the color of the worksheet tabs in my workbook?

Many users find it helpful to color-code the sheet tabs to make it easier to identify specific sheets. If you use Excel 2002 or later, right-click on the sheet tab, choose Tab Color, and then select the color for the tab.

If you're using a previous version of Excel, you cannot change the tab colors.

Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

  1. Select the source range (A1:D10 in this example).
  2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
  3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
  4. Ungroup the sheets (click the sheet tab for Sheet2)
  5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
  6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
  7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
  8. Once again, use Edit - Fill - Across worksheets.
  9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.

Creating A Database Table From A Summary Table

Category: General | [Item URL]

Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.

The worksheet below demonstrates. Range A1:E13 contains the original summary table, and columns G:I shows a 48-row database table derived from the summary table.

How to do it

The solution to creating this "reverse pivot table" is to use a pivot table! The steps below are specific to the example data shown, so you'll need to modify them slightly to work with your data.

Part 1: Creating a pivot table

  1. Activate any cell in your summary table
  2. Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
  3. In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
  4. In Step 2, choose the I will create the page fields option and click Next.
  5. In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
  6. In Step 3, select a location for the pivot table, and click the Layout button.
  7. In the Layout dialog box, you will change the default layout in the diagram. Drag both the Column button and Row button away from the diagram. This will leave the diagram with only a data field: Sum of Value. The dialog box should look like the figure below.
  8. Click OK and then Finish to create the pivot table.

Part 2: Finishing up

At this point, you will have a small pivot table that shows only the sum of all values:

  1. Double-click the cell that contains the total (outlined in yellow, above). Excel will create a new sheet that displays the original data in the form of a database table (see the figure below).
  2. The column headings will display generic descriptions (Row, Column, and Value), so you'll probably want to change these headings to make them more descriptive.

A VBA Macro to do it

If you do this sort of thing on a regular basis, you may prefer to use a VBA macro. Just copy the VBA code to a VBA module. Then activate a cell in your summary table and execute the ReversePivotTable macro. This macro uses simple looping -- no fancy pivot table tricks.

Comparing Two Lists With Conditional Formatting

Category: Formatting / Formulas | [Item URL]

Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data.

The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting.

How to do it

  1. Start by selecting the OldList range.
  2. Choose Format - Conditional Formatting
  3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
  4. Enter this formula:
  5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
  6. Click OK

The cells in the NewList range will use a similar conditional formatting formula.

  1. Select the NewList range.
  2. Choose Format - Conditional Formatting
  3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
  4. Enter this formula:
  5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
  6. Click OK

Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected range.

Getting A List Of File Names

Category: General | [Item URL]

Many users are surprised to discover that Windows does not provide a direct way to get a list of file names contained in a directory. This tip describes how to create such a list in a text file, which can then be imported into Excel.

To generate a list of file names, you'll need to use a DOS command typed in a DOS command window. To open a DOS command window:

  1. Click the Windows Start button
  2. Click Run
  3. Type "cmd" (no quotes) and press Enter. if "cmd" doesn't work, use "command".

You'll get a window like the one shown below.

Next, you need to type a DOS command to generate the file list. For example, if you would like to generate a list of all files in the root directory of drive D, type the following at the command prompt and press Enter:

dir d:\

To list the files in a particular directory, add the directory name after the drive:

dir d:\my files\

The file names will be listed in the window. Usually, you'll want these files to be sent to a file. To redirect the output to a file, use the > character and specify a file name. For example, to send the file names to a text file named filelist.txt in the root directory of drive C, use this command:

dir d:\ >c:\filelist.txt

If you would like the file list to include the files in all subdirectories of drive D, use the /s switch:

dir d:\ /s >c:\filelist.txt

The directory listing will contain lots of additional information. To get the file names only (bare format), use the /b switch:

dir d:\ /s /b >c:\filelist.txt

To find out about other options available for the DIR command (such as including file dates and times), type this command:

dir /?

After the text file is generated, you can import it into Excel by using the File - Open command.

NOTE: If you need do this on a regular basis, you may be interested in this article from Microsoft, which describes how to create a batch file that lists file names in Notepad.

Printing Just A Portion Of Your Worksheet

Category: Printing | [Item URL]

If you need to print just a small portion of your worksheet:

  1. Select the cells that you want to print
  2. Choose File - Print
  3. In the Print dialog box, choose the Selection option
  4. Click OK

If you've specified a print area, this will not change it.

Note: If your selection consists of a non-contiguous range (a multiple selection), each area is printed on a separate sheet of paper.

Avoid Printing Specific Rows

Category: Printing | [Item URL]

In some cases, you may wish to avoid printing certain rows in your worksheet. For example, the rows may contain confidential information, or intermediate results that need not be printed.

To avoid printing specific rows, you can hide the rows before you print, and then unhide the rows after printing. If your sheet has many rows that should not be printed, hiding and unhiding the rows may be a tedious process. This tip presents a way to quickly toggle the hidden status of any number of rows. It takes advantage of Excel's Group and Outline feature.

The figure below shows a simple example. In this case, rows 5, 10, 15, and 20 should not be printed.

To set up a simple outline, follow these steps:

  1. Select Row 5
  2. Choose Data - Group and Outline - Group (or, use Alt+Shift+RightArrow)
  3. Select Row 10
  4. Press F4 (this key repeats the last command)
  5. Select Row 15
  6. Press F4
  7. Select Row 20
  8. Press F4

The preceding steps created a simple outline on the worksheet, and the outline symbols are displayed along the left side of the sheet. You can hide all of the "grouped" rows by clicking the small "1" button at the top of the outline symbol area (see the figure below).

After you've printed the sheet, click the "2" button to redisplay all of the rows.

Note: To hide the outline symbols, press Ctrl+8. The outline remains, but the symbols are hidden. To re-display the outline symbols, press Ctrl+8 again.

Alternate Row Shading Using Conditional Formatting

Category: Formatting | [Item URL]

One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task.

  1. Select the range that you want to format
  2. Choose Format, Conditional Formatting
  3. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula:
  4. Click the Format button, select the Patterns tab, and specify a color for the shaded rows.
  5. Click OK twice to return to your worksheet.

The best part is that the row shading is dynamic. You'll find that the row shading persists even if you insert or delete rows within the original range.

Duplicate Repeated Entries In A List

Category: Formatting | [Item URL]

You've probably seen an Excel worksheet (like the sheet on the left, below) in which one entry in column A applies to several rows of data. Sort such a list and you get a real mess, because rows with empty cells in the sort column move to the top or bottom (depending on the sort order).

When a list is small, you can enter the missing cell values manually. But if your database is huge, you need a better way of filling in those cell values. Here's how:

  1. Select the range (A3:A14 in the example above)
  2. Press Ctrl-G to get the Go To dialog box.
  3. In the Go To dialog box, click Special.
  4. Select the Blanks option.
  5. Type = followed by the address of the first cell with an entry in the column (=A3 in the example above), and press Ctrl-Enter.
  6. Reselect the range and choose Edit, Copy.
  7. Then select Edit, Paste Special, choose the Values option, and click OK.

The missing entries will be filled in, as in the sheet on the right in the above figure.

Working With Fractions

Category: Formatting | [Item URL]

Some types of data, such as stock market quotes, normally display as fractions, not decimals. To enter a fraction in Excel, type the whole number (or integer) followed by a space, and then type the fraction, using a slash (for example, 5/8). If you type only a fraction, Excel may interpret it as a date (so it might read 5/8 as May 8). To avoid this mistranslation, enter 0, a space, and then the fraction.

When you enter a fractional value, Excel automatically applies a fraction number format that reduces it to the smallest possible denominator. For example, if you enter 16 2/8, Excel displays the number as 16 1/4. In some cases, however, you'll want the fractions to use a common denominator. For example, you might want the value 16 2/8 to be shown as 16 4/16. To obtain this result, select your cells and choose Format, Cells. Then select the Number tab and choose Fraction from the Category list. Finally, select the desired number format from the Type list.

The worksheet below shows some examples of numbers expressed as fractions. Column B shows the numbers produced using Excel's default formatting. Column C has the same values formatted as 16ths.

You can also express fractional data using a decimal point. For instance, the number 9 4/16 could appear as 9.04. Here, the digits to the right of the decimal represent 16ths. To display values in this format, use Excel's DOLLARFR() function. It's available only when the Analysis ToolPak is installed (select Tools, Add-ins to install it). The DOLLARFR() function takes two arguments: the number and an integer for the denominator. The formula =DOLLARFR(9.25,16), for example, returns 9.04.

This function is also useful for nondollar data. So, if you work with feet and inches, you can represent 11.5 feet as 11.06 (11 feet, 6 inches) by using this formula:


The value will then appear as "11 [feet] 6 [inches]."

The DOLLARFR() function is for display only. You can't use the value it returns in other calculations or in charts. To perform calculations on such values, reconvert them into decimal values by using the DOLLARDE() function (also part of the Analysis ToolPak).

Page 11 of 17 pages
[Previous page]   [Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2018, J-Walk & Associates, Inc.
Privacy Policy