196 |
POWER EXCEL WITH MR EXCEL |
|
|
Figure 482 F2 says to look at cell C1.
The cell reference in the INDIRECT can be calculated on the fly. In this example, the VLOOKUP points to a different worksheet based on the quarter number in column B. INDIRECT uses concatenation to build something that looks like a worksheet reference.
Figure 483 Q2!A1:B99 is calculated on the fly inside of the INDIRECT.
Additional Details: If you have used range names, the value inside of INDIRECT can point to a range name. This creates some interesting lookup possibilities. For an example, see "Why Use the Intersection Operator?" on page 115.
TABLES ARE LIKE A DATABASE IN EXCEL
Problem: Excel isn’t like Access. I am an Access person and Excel annoys me.
Strategy: If you have database-like data in Excel, define the data as a table.
Many spreadsheets in Excel contain a two-dimensional table of data. You have headings in the first row, and each row of the worksheet represents a different record in a table.
Because a common task in Excel is dealing with tables, Excel has added several features for dealing with tables. One of the best benefits of the table functionality is that charts and pivot tables based on a table will automatically grow with the table.
To turn on the features, select a single cell in the dataset and press Ctrl+T. Excel will assume your table extends to either the edge of the spreadsheet or to a blank row and blank column. The Create Table dialog will ask you to confirm the range for the table and that the first row contains headers.
PART 2: CALCULATING WITH EXCEL |
197 |
|
|
Figure 484 Excel guesses the current region as the address for the table.
When you apply a table, you will notice the following features:
●● Excel applies a default table formatting. You can change to another style using the Table Styles gal- lery on the Table Tools Design tab.
●● Excel turns on the Filter dropdowns on each heading. You can use these dropdowns to sort by a column or to filter a column.
●● If you are in the table and scroll so the headings are not visible, the headings will replace column letters A, B, C and so on. The Filter dropdowns will remain available after the first row scrolls out of view.
●● You can add totals to the bottom of the dataset by using the Total Row check box in the Table Tools
Design tab.
The following features are not immediately visible, but will work:
2
●● Any new data typed in the blank row below the table will be made part of the table. This means that any charts, pivot tables, or formulas that refer to the table will automatically apply to the new data.
●● A resize handle in the bottom-right corner of the table allows you to drag to manually extend the table to include additional columns.
●● You can use the Table Style Options check boxes to turn on alternate formatting for the first column, last column, header row, total row, or to apply alternating shading to rows or columns.
●● Any formulas that point to columns in the table will be written in a new table nomenclature. Enter a formula once and Excel will copy it to all rows of the table.
Gotcha: the filter dropdowns cover up some of the headings. You will find that you end up left-aligning headings so that you can read the headings. You can turn off the filter dropdowns by using Data, Filter.
Gotcha: sometimes you turn on the table functionality to quickly apply a format to the table. It is okay to use Ctrl+T to create and format a table and then immediately use Table Tools, Convert to Range to turn the table back into a normal range. The table formatting remains!
Additional Details: For more information on table, check out the book Excel Tables by Zack Barresse and Kevin Jones.
DEALING WITH TABLE FORMULAS
Problem: Once I define something as a table, the formulas are strange.
Strategy: You are seeing the new structured referencing in a table. Here is how it works.
Suppose you want to add a Profit % column to a table. Follow these steps: 1. Enter a heading of GP% in cell H1.
2. Format cell H2 as a percentage. Do this before you enter the formula.
3. In cell H2, type an equals sign. Click the Profit in G2. Type a divide sign. Click the Revenue in F2. You will already notice something different: Excel is building a formula of =[@Profit]/[@Revenue].
Figure 485 The table formula syntax is like the natural language syntax.
198 |
POWER EXCEL WITH MR EXCEL |
|
|
4.Press the Enter key to complete the formula. Excel automatically copies the formula down to all the rows in your dataset!
The automatic copying of the formula is a great feature. However, there will be a few times when you do not want this to happen. If so, find the AutoCorrect dropdown and open it. You will have choices to turn of the calculated column or to turn off the feature permanently.
Figure 486 Override automatic formula copying.
RENAME YOUR TABLES
Problem: Is a formula such as =SUM(Table1[Revenue]) supposed to be meaningful?
Strategy: When you create a table by pressing Ctrl+T, Excel gives the table a generic name, such as
Table1, Table2, and so on. If you rename the table, the formulas will start to make more sense. Here’s what you do:
1. Convert a range to a table by selecting one cell in the range and pressing Ctrl+T and clicking OK. 2. Click in the Table Name field in the Properties group in the Design ribbon and type a new name for
the table. A name such as tSalesData might be more meaningful than Table1.
Results: Excel will rewrite any formulas that point to the table to use the new table name. For example, it will change the =SUM(Table1[Revenue]) you asked about to =SUM(SalesData[Revenue]).
CHARTS , VLOOKUP & PIVOTS EXPAND WITH THE TABLE
Problem: I always have to add new data to the bottom of my data. Then, I have to redefine the charts, pivot tables, and lookup tables that are based on this data.
Strategy: Using tables simplifies this process. Even if you have existing charts, VLOOKUP, and pivot tables, you can benefit from changing the data set to a table.
Below, a chart is based on a table that contains 4 weeks and 3 months.
Figure 487 This chart is based on a table.
PART 2: CALCULATING WITH EXCEL |
|
199 |
|
|
|
If you enter new data next to the table, the rows and |
|
|
columns will be added to the table and automatically |
|
|
added to the chart. |
|
|
Gotcha: Tables were designed during the Excel 2007 |
|
|
development cycle. While one team was designing |
|
|
tables, the charting team was busy completely rewrit- |
|
|
ing the chart engine. Time was running short, and the |
|
|
chart team opted not to support table syntax in the |
|
|
SERIES formula. |
|
|
Additional Details: Pivot tables will expand with |
|
|
the table, but you have to click the Refresh button on |
|
|
the PivotTable Tools Options ribbon tab to refresh the |
|
|
cache. This is still far easier than redefining the data |
|
|
range like you would have to do for non-table pivots. |
|
|
|
Figure 488 The chart automatically grows |
|
|
because it is based on the table. |
|
BEFORE DELETING A CELL, FIND OUT IF OTHER CELLS RELY ON IT
Problem: I am about to delete a section of a worksheet that I believe is no longer being used. However, I |
|
2 |
|
||
know that if I delete the cell, and some other far-off range relies on the cell, the far-off range will change |
|
|
to the dreaded #REF! error. How can I determine if any other range refers to this cell? |
|
|
Strategy: You can select the cell that you are considering for deletion and then select Formulas, Trace |
|
|
Dependents. (Dependents are other cells that rely on the current cell for calculation.) |
|
|
Blue arrows will draw from the active cell out to any dependents. Below, , you can see that cell F4 is used |
|
|
to calculate H4. |
|
|
Figure 489 Blue arrows point to dependent cells on this worksheet.
If a dependent is on another worksheet, Excel will draw a black arrow to the other worksheet icon. Doubleclick the line that leads to the other worksheet icon. Excel will show you a list of the off-sheet dependents.
Additional Details: If you re-click Trace Dependents, Excel will draw second-level dependents. Below, you can see that F4 is used to calculate H4, and H4 is used to calculate
D15 and E20.
If you click Trace Dependents several times, you will see all of the formulas that would change to #REF! if you delete cell C4.
You also have a big mess on your spreadsheet! To get rid of all arrows, choose Remove All Arrows.
Gotcha: Someadvanced functionssuchas=INDIRECT(“F” & D4/600) might be pointing to your target cell and will not be detected by the Trace Dependents command.
Figure 490 Second-level dependents.