How To Create Power Pivot In Excel

Excel workbooks are the next step after you've mastered the single Excel spreadsheet. Going back to the ledger idea we used in our Excel spreadsheets guide, think of the workbook as the ledger (or book), and the spreadsheets as the pages inside that book.

We'll dive deep into Excel workbooks in this guide, starting with the basics and adding more over time—so bookmark this page and come back to keep learning more.

Spreadsheet vs. workbook: How they fit together

Spreadsheets and workbooks are closely related. When you open Excel, the software displays a blank worksheet (called sheet1). If you saved the file right now (with just the one spreadsheet), it would actually be called a Workbook—it's just a workbook with one page.

Some applications may never require more than one spreadsheet in your workbook. Most likely, though, as your Excel knowledge grows, so will your workbooks. You'll soon discover that many of your spreadsheets are related, or they're getting too large or too slow to manage. That's when it makes sense to group them together in a workbook, which just means adding more spreadsheets and then spreading the workload out across the multiple pages (or sheets). To add more spreadsheets to your workbook, click the circled plus + sign at the bottom of your spreadsheet, on the tabs taskbar. Each time you click it, another sheet tab appears.

Before we start, a few notes about workbooks and your PC's resources. The number of spreadsheets allowed in a single workbook is limited only by your system's memory, which is very different between the 32-bit version (which maxes out at 2GB) and the 64-bit version (which expands all the way out to 8TB). Remember also that Excel has its own memory manager and memory limits, so even if your system supports the maximum memory, there's no guarantee that your workbooks will have access to all of that memory.

01 add spreadsheets to the workbook — Add spreadsheets to the workbook by clicking the circled plus sign in the tab taskbar that runs along the bottom of any workbook.

How Excel workbooks make better spreadsheets

Because the workbook is just the binder that holds the spreadsheets, all the options and features available in a single spreadsheet are also available in the workbook. The most productive benefits include:

Global macros—that is, macros that work on every sheet in the workbook
Linked formulas
Relational data that's shared across multiple spreadsheets and connected by a unique key field
Grouping spreadsheets so changes to one are applied to all
Creating and using pivot tables and summaries
Printing reports and creating charts from data stored in multiple spreadsheets

We'll get to those features in future updates. For this first installment, we'll show you how the greatest powers of the workbook lie in the database functions and the capacity to calculate spreadsheets three-dimensionally. This means you can calculate columns (down), rows (across), and/or sheets (deep).

Three-dimensional Excel workbooks

The best example to illustrate this concept is a Year-to-Date workbook. The premise for this task is to track the monthly and yearly overhead expenses for a small company that's owned by five different investors. The percentage that each investor pays is based on his/her percentage of ownership in the company (which is also the percentage that each investor earns on the company's profits).

Imagine you have one sheet for each month, plus another sheet for all the year-to-date totals. Before you add all those extra spreadsheets, create the "master" or " year-to-date totals" spreadsheet.

1. Double-click the Sheet1 tab and change the name to Totals.

2. Enter the word Overhead in A1. In B1 through F1, enter five names (these are the names of the five owner/investors). Enter the phraseMonthly Totals in G1.

3. In B2 through F2, enter the percentage that each owner has invested in the company. In G2, enter the totals of B2 through F2, so you can ensure that your percentages do not exceed 100%. For example, enter this: =sum(B2:F2).

4. Next, enter the company's overhead items in column A (from A3 through A14), then add the word Totals in A15. You can choose your own items or use ours: Equipment Rentals, Internet Service, Cell Phone Service, Landlines, Electricity, Heat, Water, Trash/Garbage Pickup, Alarm Service, Maintenance, Cleaning Services, and Landscaping.

02 set up and define the master year to date spreadsheet — Set up and define the master Year-to-Date spreadsheet.

Now that you have the master spreadsheet set up and defined, copy this sheet 12 times (on 12 newly inserted spreadsheets).

5. Right-click the Totals tab and select Move or Copy from the popup menu.

6. Click Move to End and then check the box that says Create a Copy.

Quick tip: Use the Ctrl key to copy the master spreadsheet multiple times. Note that you can only copy the available tabs. Two tabs copied would create four tabs total, four tabs would create eight total, and so forth. To copy more than the available tabs would require you to write a VBA program.

7. Hold down the Ctrl key, then click the Totals tab and the Totals(2) tab.

8. Click Move to End again and check the box that says Create a Copy again. Now you have four tabs.

9. Hold down the Ctrl key, then click the Totals tab, Totals (2) tab, Totals (3), and Totals (4) tabs.

10. Click Move to End again and check the box that says Create a Copy again. Now you have eight tabs.

11. Repeat the steps above to create four more tabs, then double-click each tab, and enter the name of a month on each of the 12 new tabs.

03 copy the master spreadsheet 12 times — Copy the master spreadsheet 12 times, then name the tabs for each month.

Enter all the overhead expenses for January on the Jan (January) spreadsheet, and so forth through December. You can enter the formulas for each individual spreadsheet: For example, the Overhead Expenses Totals in Row 15 (B15 through G15) for each month as needed, or group all the spreadsheets and enter these formulas only once.

12. Move your cursor to the Home position in the Totals spreadsheet. Then hold down the Ctrl key and click each worksheet tab (Totals first, then Jan through Dec) to group all the sheets in this workbook. Note that when selected, the tab name is underlined and turns bold.

Quick Tip: To group all the spreadsheets in a single workbook consecutively, click the tab of the first spreadsheet, press and hold the Shift key, then click the tab of the last spreadsheet and all the sheets are selected simultaneously.

13. Current position: Home (A1) in the Totals spreadsheet. Move your cursor to B15 and enter this formula: =sum(B3:B14). Press Enter. You could also enter the function, then point to the range.

14. Copy the formula in B15 to C15 through G15. If you have not entered any data into the spreadsheet yet, the totals will all show zeroes.

Another quick tip: If you like your numbers centered in the cells, now would be a good time to format the entire spreadsheet, while it's grouped, so it's a one-time task.

15. Highlight the table and choose center (left/right) and center (up/down). You can also format the cells for numbers (with two decimal places) or as dollars.

16. Click through the tabs at the bottom and double-check that you had ALL the tabs grouped. If all the formulas and formats are present and the same, then your group was successful.

04 group all the tabs then enter — Group all the tabs, then enter the column and row formulas and format the data.

17. Next, enter some data in the Monthly Totals column (G) on the Jan and Feb spreadsheets. This would be the total amount paid for each overhead item for the current month.

Once the current monthly totals are entered, we can add the percentage formulas, which determine the amount that each owner pays. For example, if the total monthly cost for Equipment Rentals is $9500.00, Jackson's portion is 31% of that amount.

18. Group the monthly spreadsheets again, but DO NOT include the Totals sheet this time. Enter the percentage formulas.

Note: Use the function key F4 to make the cell addresses that don't change absolute, which means the cell address does not change when you copy the formula (called a relative reference). Relative references, which is the default in Excel, means that when you copy the formulas, the cell addresses in the formula change based on the relative position of the columns and rows.

19. Enter the following formula in cell B3: =SUM($G3*B$2). Copy this formula to B4 through F14.

20. Highlight the full table and select the $ format.

The column and row formulas are entered and working properly. Now enter the depth or 3D formulas. These formulas will calculate the data from every month (Jan through Dec) and print the answer on the Totals sheet.

21. Return to the Totals spreadsheet and enter this formula in cell B3: =SUM(Jan:Dec!B3).

22. Copy this formula across from B3 to C3 through G3. Then highlight this row and copy down to B4 through G14.

23. Highlight the range, select the $ format, and adjust the column width of any columns that display a string of pound signs (hash tags).

05 the complete year to date workbook with formulas — The complete year-to-date workbook with formulas that calculate vertically, horizontally, and dimensionally

Once this workbook is set up and the formulas are in place, you can reuse it forever. Just copy it and change the data, which is easy because the only cells with dynamic data are in column G, Monthly Totals. Everything else is a label or a calculation.

Important note: If you need to add more overhead expenses items (or delete them), use the Insert > Sheet Row feature and be sure to insert the new rows INSIDE the existing table—that is, before the last record and after the first record. For example, insert new rows between A4 and A13. If you insert new rows outside of the defined range, those rows will NOT be included or calculated by your existing formulas, which means all your totals will be wrong.

Use the same logic for inserting columns and additional sheets. Once the new rows are inserted, if you don't like the order of the new data, you can always re-sort the range.