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.

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.

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.

Q. The formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas. Can I set things up so the cell cursor moves only to the input cells?

Yes. You've already unlocked your input cells and ensured that all other cells are locked. By default all cells are locked, but you can change that by using the Protection tab of the Format Cells dialog box. Select the cells to be changed and choose Format, Cells. In this case, the input cells are unlocked and all other cells are locked.

Protect the worksheet in Excel 97 by using Worksheet, Protect or Tools, Protection, Protect Sheet (you can specify a password to keep others from "unprotecting" the sheet). Once the sheet is protected, press Tab to move the cell pointer to the next unlocked cell.

This does not prevent the user from selecting unlocked cells using the cursor keys. To make those cells unselectable, change the worksheet's EnableSelection property. Select View, Toolbars, Control Toolbox to display the Control Toolbox toolbar. Click the Properties button to display the Properties box for the worksheet, then click the cell labeled "xlNoRestrictions" and use the drop-down list to change the EnableSelection property to xlUnlockedCells. Close the Properties box. As long as the worksheet is protected, users cannot select the locked cells on the worksheet.

This procedure does not save the EnableSelection property setting with the workbook. To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing. Then double-click the item labeled ThisWorkbook and enter the following VBA code:

Private Sub Workbook_Open()
  Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

This macro executes whenever the workbook is opened, and sets the EnableSelection property of Sheet1 to xlUnlockedCells. The technique can be circumvented by changing the EnableSelection property to its default value (xlNoRestrictions). Few users know about this dodge, however.

If you find yourself with many workbooks open at the same time, you may be interested in a "hidden" command that will close all workbooks in one fell swoop.

The trick is to press Shift while you click the File menu. When you do so, the Close command turns into the Close All command. When you select this menu item, Excel will close all of the currently open workbooks. If any of them have not been saved, you'll get the standard prompt asking if you want to save your workbooks.

By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks.

A better approach is to change the default. Select Tools, Options and click General in the Options dialog box. Then change the setting for "Sheets in new workbook". Now all new workbooks will have the number of sheets you specified.

I recommend that you change this setting to 1.

If your eyesight isn't what it used to be, you may want to increase the size of the sheet tabs displayed in your Excel workbooks.

This parameter is determined by a Windows system-wide setting. To change it, select Start, Settings, Control Panel and double-click Display. In the Display Properties dialog box, click the Appearance tab, and choose Scrollbar from the Item list. Adjust the sheet tabs until they're the size you want. Be aware that this setting affects the scroll bars in all your applications.