Category: Excel Tips and Tricks

How to Create an Excel Pivot Table from Multiple Sheets

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/

 

Excel 2007 Pivot Table Tutorial

This post is originally from About.com Spreadsheets
http://spreadsheets.about.com/b/2010/04/09/excel-2007-pivot-table-tutorial-2.htm

 

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

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:

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

Earlier on grid lines: Hide Gridlines in Excel
More Quick Excel Tips and Tricks

This post is originally from Pointy Haired Dilbert: Learn Excel Online - Chandoo.org
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/47T9HltwoUo/

 

Excel Pie Chart Tutorials

This post is originally from About.com Spreadsheets
http://spreadsheets.about.com/b/2010/03/31/excel-pie-chart-tutorials-3.htm

 

All together now! Merging sheets with ActiveData for Excel

In this tip we discuss another powerful feature of ActiveData for Excel, the Merge Sheets command.

The Merge Sheets command allows you to combine rows from the current worksheet with rows from another worksheet based on one or more matching group columns. The result is a new worksheets containing rows made up of cells from both worksheets.

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.

Minimum Distance

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.

Maximum Distance

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.

Maximum Matches

Since this process involves comparing every group value in Sheet1 with every group value in Sheet2, this value is used to limit the number of possible matches.

You can click below to see a video from our website:

Merging (Joining) Worksheets

Example

  • 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 support@informationactive.com or 613-569-4675 x 175.

The ActiveData Team