Page 101 - Excel 2013 All-in-One For Dummies

Designer Spreadsheets 83
Economy
Economy is an important consideration because when you open a work- book, all its data is loaded into your computer’s dynamic memory (known simply as memory). This may not pose any problems if the device you’re run- ning Excel 2013 on is one of the latest generation of PCs with more memory than you can conceive of using at one time, but it can pose quite a problem
if you’re running Excel on a Windows tablet or smartphone with limited memory or share the workbook file with someone whose computer is not
so well equipped. Also, depending on just how much data you cram into the workbook, you may even come to see Excel creep and crawl the more you work with it.
To help guard against this problem, make sure that you don’t pad the
data tables and lists in your workbook with extra empty “spacer” cells. Keep the tables as close together as possible on the same worksheet (with no more than a single blank column or row as a separator, which you can adjust to make as wide or high as you like) or — if the design allows — keep them in the same region of consecutive worksheets.
Functionality
Along with economy, you must pay attention to the functionality of the spreadsheet. This means that you need to allow for future growth when selecting the placement of its data tables, lists, and charts. This is especially important in the case of data lists because they have a tendency to grow longer and longer as you continue to add data, requiring more and more rows of the same few columns in the worksheet. This means that you should usually consider all the rows of the columns used in a data list as “off limits.” In fact, always position charts and other supporting tables to the right of
the list rather than somewhere below the last used row. This way, you can continue to add data to your list without ever having to stop and first move some unrelated element out of the way.
This spatial concern is not the same when placing a data table that will total the values both down the rows and across the columns table — for example, a sales table that sums your monthly sales by item with formulas that cal- culate monthly totals in the last row of the table and formulas that calculate item totals in the last column. In this table, you don’t worry about having to move other elements, such as embedded charts or other supporting or unre- lated data tables, because you use Excel’s capability of expanding the rows and columns of the table from within. As the table expands or contracts, sur- rounding elements move in relation to and with the table expansion and con- traction. You do this kind of editing to the table because inserting new table rows and columns ahead of the formulas ensures that they can be included in the totaling calculations. In this way, the row and column of formulas in the data table acts as a boundary that floats with the expansion or contrac- tion of its data but that keeps all other elements at bay.
Book II Chapter 1
Building Worksheets