Excel Workbooks: Three-dimensional workbooks and other number-crunching powers


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’d 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 JD Sartain / PC World

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).




Reply

Select Language