Category: Excel Tips and Tricks
If Excel data is on different sheets, you can create a pivot table using multiple consolidation ranges. It’s better if the data is all on one sheet, but if you don’t have that option, multiple consolidation ranges will pull all the data into one pivot table.
In Excel 2003 you can open the PivotTable and PivotChart wizard by choosing Data | PivotTable and PivotChart Report. There’s no equivalent command on the Ribbon in Excel 2007, but you can press Alt+D, then type P to open the wizard.
The pivot table from multiple sheets isn’t as flexible as a regular pivot table — all the data fields use the same summary function, and there’s only one row field. However, while you’re setting up the pivot table you can create one or more page fields, and create labels for the data ranges.
Read the Instructions
There are written instructions, and details on the pivot table limitations, on the Contextures website: Excel Pivot Table Tutorial — Multiple Consolidation Ranges
Watch the Video
This video shows you the steps in Excel 2007, to create the pivot table from multiple sheets, and set up a page field.
This post is originally from IANet Excel Feed http://blog.contextures.com/archives/2010/04/16/how-to-create-an-excel-pivot-table-from-multiple-sheets/
This post is originally from About.com Spreadsheets http://spreadsheets.about.com/b/2010/04/09/excel-2007-pivot-table-tutorial-2.htm
These days I rarely use Excel 2003. But when I do open the trusty old software, I always look for opportunities to improve my productivity with it. And I am pleasantly surprised to find a shorter and faster way to turn off grid lines on spreadsheets in Excel 2003. (I like my spreadsheets without any grid lines, like a clean slate).
I usually go to Tools > Options > View Tab and then uncheck the “gridlines” checkbox.
This takes about 5 clicks and a lot of mouse commute. So much that I often feel like taking a detour for a sip of coffee. But the new and simpler method I found yesterday is to use the forms tool bar (which is turned on by default in my comp). In forms toolbar you find the “toggle gridlines” button which can quickly turn on / off the grid lines. See below:
This post is originally from Pointy Haired Dilbert: Learn Excel Online - Chandoo.org http://feedproxy.google.com/~r/PointyHairedDilbert/~3/47T9HltwoUo/
This post is originally from About.com Spreadsheets http://spreadsheets.about.com/b/2010/03/31/excel-pie-chart-tutorials-3.htm
In this tip we discuss another powerful feature of ActiveData for Excel, the Merge Sheets command.
Each worksheet has to contain one or more common columns that can be used to match rows between worksheets. These are called the group columns and are selected in the Merge Rows Based On These Groups section.
In the Merge These Columns and the With These From Sheet2 sections, select the desired columns for the output worksheet.
Rows in Sheet1 that match a row in Sheet2
When checked, group column values that appear in both Sheet1 and Sheet2 are included in the output worksheet.
Rows in Sheet1 that don’t match a row in Sheet2
When checked, group column values that appear in Sheet1 but are missing in Sheet2 are included in the output worksheet with the missing values from Sheet2 blanked out.
Rows in Sheet2 that match a row in Sheet1
When checked, group column values that appear Sheet2 and Sheet1 are included in the output worksheet.
Rows in Sheet2 that don’t match a row in Sheet1
When checked, group column values that appear in Sheet2 but are missing in Sheet1 are included in the output worksheet with the missing values from Sheet1 blanked out.
Use Levenshtein Distance Fuzzy Matching
The Levenshtein distance measures the difference between two text values in terms of the number of keystrokes it would require to turn the first text value into the second text value.
Before computing this difference, all puctuation characters and spaces are removed from both values.
The lower the difference, the more similar the two values are. Identical values (exact matches) have a difference of 0.
For example, the distance between “123 Main Street” and “123 Main St” is 4. In this case all it takes is 4 taps on the backspace key or delete key to transform the first value into the second value.
Here are a some more examples:
- The distance between “123 Main St” and “123-1 Main St” is 2 since only 2 characters need to be inserted.
- The distance between “153 Main St” and “123 Main St” is 1 since only 1 character needs to be overtyped.
- The distance between “153 Main St” and “153 Main St” is of course 0 since the values are the same.
- The distance between “John Smith” and “John M. Smith” is 2 since the letter M and a dot need to be inserted.
The following parameters determine what to treat as a possible match.
This is the minimum difference. If you want include exact matches, set this to 0, otherwise set it to 1 or higher. The lower this value is, the more potential there is for matches and false positives.
This is the maximum allowable difference. Items whose difference is greater than this value will not be considered as possible matches. The higher this value, the more potential there is for matches and false positives.
You can click below to see a video from our website:
- Open ActiveData’s Sample Workbook.
- Select the Invoices Sheet.
- Select Merge Sheets from the ActiveData Menu.
- In the Select a Sheet to Merge dialog box, choose the SalesPeople sheet.
- In the Merge Rows Based on these Groups section, select Salesperson.
- In the Merge These Columns section, select the columns you want to include in the output worksheet.
- In the With These From Sheet2 section, select Name.
- Click Finished.
Thanks for buying ActiveData.
As always if you need help please contact us at email@example.com or 613-569-4675 x 175.