PART 3: WRANGLING DATA |
233 |
|
|
HOW TO SET UP YOUR DATA FOR EASY SORTING AND SUBTOTALS
Problem: I want to be able to use the powerful data commands such as Sort, Filter, Subtotal, Consolidate, and PivotTable. Is there any special way I should set up the data to begin with?
Strategy: You need to follow all the rules to keep your data in list format:
Rule 1: Use only a single row of headings above your data. If you need to have a two-row heading, set it up as a single cell with two lines in the row. See "How to Fit a Multiline Heading into One Cell" on page 233.
Rule 2: Never leave one heading cell blank. You will find that you do this if you add a temporary column. If you forget to add a heading before you sort, this will completely throw off the IntelliSense, and Excel will sort the headings down into the data.
Rule 3: There should be no entirely blank rows or blank columns in the middle of your data. It is okay to have an occasional blank cell, but you should have no entirely blank columns.
Rule 4: If your heading row is not in row 1, be sure to have a blank row between the report title and the headings.
Rule 5: Formatting the heading cells in bold will help the Excel’s IntelliSense module understand that these are headings.
Gotcha: List format won’t help at all if your data is only two columns wide.
Results: If you follow the list format rules, Excel’s IntelliSense will allow all the data commands to work flawlessly.
HOW TO FIT A MULTILINE HEADING INTO ONE CELL
Problem: In “How to Set Up Your Data for Easy Sorting and Subtotals,” you say that headings should occupy only one row to allow for easy sort- ing. My manager requires that I format a report to have the heading “Pri- or Year” split, with “Prior” in one row and “Year” in a second row. How can I make my manager happy while also following the list format rules?
Strategy: This is a very real problem, where form meets function. The right thing to do in Excel is to have “Prior Year” in one cell. But some managers absolutely, positively want the formatting to be exactly as they specify. Luckily, there is a strategy that makes it possible to make the manager happy and to correctly set up the data set in Excel, too.
In cell X5, you type the word Prior. Then you hold down
Alt while pressing Enter and type the word Year. The
Alt+Enter combination adds a linefeed character in the cell. You can delete the old heading in X4 by mov- ing the cell pointer there and pressing the Delete key.
3
Figure 577 Your manager wants this heading on two rows.
Figure 578 Use Alt+Enter to go to the next line.
Results: You have a single cell that contains two lines of text. The cell will work as a heading in pivot tables, subtotals, sorting, and so on.
Additional Details: Using
Alt+Enter automatically turns on the Wrap Text option for the cell.
You could also turn on the Wrap
Text option by choosing Home,
Wrap Text icon.
Figure 579 Wrap Text finally has an icon starting in Excel 2007.
234 |
POWER EXCEL WITH MR EXCEL |
|
|
Turning on Wrap Text in this manner will probably work for a brief heading like “Prior Year.” However, if you want to have control over a long heading, such as “Prior Year Re- sults (Adjusted for Spin-off of the Widget Division),” then it is better to use Alt+Enter to specify exactly where the line break should occur. Wrap text uses a somewhat haphazard splitting of words.
Figure 580 Excel decides where to break lines.
As you make this column wider, Excel changes the way the words are wrapped. It is frustrating to keep adjusting the column widths until you get the words to wrap correctly.
Gotcha: After resizing a cell with Wrap Text, you often end up with a row height that is too tall. To correct this, you select the cell and then choose
Home, Format dropdown, Autofit Row Height.
Figure 581 Adjusting column widths to change line breaks rarely works.
Using Alt+Enter gives you absolute control over where the heading breaks. To create the figure below, type Prior Year Results <Alt+Enter> (Adjusted for the <Alt+Enter> Spin-off of the <Alt+Enter> Widget Division). You can make the column wider and center it for the perfect-looking cell.
Figure 582 Press Alt+Enter to wrap the text at logical points.
NO TINY BLANK COLUMNS BETWEEN COLUMNS
Problem: My Manager wants tiny blank columns between the columns.
Strategy: Plan on restating your numbers to the Securities and Exchange Commission. Tiny blank col- umns are a recipe for disaster. Someone will sort part of the data and not all of the data.
Most managers who demand this are doing it to make the bottom border under the headings look better.
Figure 583 Tiny blank columns are dangerous.
The manager here is using a bottom border to create the lines under the headings. He ends up using the bottom border because underlines just don’t look right. They only extend as long as the heading.
Figure 584 Underlines rarely make the manager happy.
PART 3: WRANGLING DATA |
235 |
|
|
There is a solution that will make the manager happy. It won’t be found in the ribbon. Remember that the options in the ribbon are there to make Excel novices happy. If you are reading this book, you frequently have to go beyond the ribbon. In the ribbon, they offer two types of underlines.
Skip the ribbon choices. Instead, click on the dia- log launcher at the bottom of the Font group of the
Home tab of the ribbon.
Figure 586 Click the dialog launcher.
The underline dropdown in the Format Cells dialog offers four choices instead of two. Choose Single Ac- counting underline.
Figure 585 Two underline types in the ribbon.
Figure 587 The good choices are not in the ribbon. |
3 |
|
The result are bottom underlines that extend almost all the way across the cell, but not quite all the way. When printed, this will give the same look as back in Figure 583.
Figure 588 These underlines appear similar to gaps between columns.
Alternate Strategy: If your manager still demands the blank columns, you can put a word, such as
“blank” in the headings of the blank columns. Change the font color to white to no one sees those headings when printed. This will allow the entire data set to be treated as a contiguous set of columns.
HOW TO SORT DATA
Problem: I have sales data in a worksheet. I would like to sort the data by product within customer.
Figure 589 Sort by product within customer.
236 |
POWER EXCEL WITH MR EXCEL |
|
|
Strategy: Here’s what you do:
1. Select one cell within your data. The one cell can be in the heading row or any data row. Select Data, Sort. Whereas Excel 2003 only allowed sorting by three fields, Excel now offers up to 64 sort levels. Rather than the old dialog with the three fields, you now start with one field and add levels as neces- sary.
2. Choose Sort By dropdown, Customer.
3. Click the Add Level button. A new row will appear in the Sort dialog. Choose Then By dropdown,
Product.
Figure 590 Build as many sort levels as necessary.
4. Leave the Sort On and Order dropdowns at their default values. If, for some reason, you wanted the customers sorted in descending alphabetical order, you could change A to Z to Z to A. That might make more sense if you were sorting by revenue, but it is not likely that you need the customers sorted in reverse alphabetical sequence. If your data is set up correctly as outlined in ”How to Set up Your Data for Easy Sorting and Subtotals,” Excel will properly guess that your list has a header row.
5. Click OK to sort. Because Customer was the first sort key, all the records for “ABC Company” will sort to the top. Records for “ABC GMbH” will appear next.
Figure 591 The data is sorted.
Additional Details: When there is a tie—for example, the four records for “ABC GMbH”—those records will be sorted in ascending order by the product field. For instance, the ABC product record appears before the DEF product field. If there is still a tie, the records will remain in their original sequence from before the sort.
Alternate Strategy: If your data is properly set up in list format, you can select a single cell in the data and then use the AZ or ZA buttons on the
Data tab.
Note that these same icons are also in the Sort & Filter dropdown on the Home tab. If you don’t want the extra click of opening the dropdown or going to the Data tab, you can add the icons to the Quick
Access Toolbar.
If you use either method, Excel will sort the data by the column in which the cell pointer is currently located. Because Excel resolves ties by leaving the previous sequence in place, you can sort by product within customer. First, you select a cell in the Product field and click AZ to sort by product. Next, you select a cell in the Customer field and click AZ to sort by customer. The data will be sorted by customer, with ties sorted by product.
You can click the ZA button to sort in descending order.