If you’ve been using Excel since the Stone Age (like me!), you might be avoiding Power Query, Power BI, and all those other “Power-y” tools that the kids are using now. However, those tools aren’t going away, and pretty soon your employer, or your clients, will expect you to know something about them. Here’s how you can learn more about Power BI, to see what all the fuss is about.
Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven’t been using it for big projects, or client work.
Recently though, I’ve been testing Power Query, and the things that it can do are very exciting. I’m just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.
Once the data is combined, you can filter and sort it, all in one place. Or, create a pivot table from the combined data. It’s much easier, and with better results, than the old methods of working with Multiple Consolidation Ranges.
Remember, Sunday October 17th is Spreadsheet Day, so you’d better start planning your celebrations. You could start the day with a big bowl of Chex cereal — each bite looks like a little spreadsheet. For dessert at the end of the day, have some pie, or bars, while you dream about charts.
As my contribution to Spreadsheet Day 2010, I’ll list the top 5 Excel tips that I read on Excel blogs over the past year. These were Excel articles that solved problems I’d encountered, or got me thinking about new techniques to try. You can read these articles on Sunday, while you’re celebrating Spreadsheet Day. Maybe your kids would enjoy them as a bedtime story!
There were many other Excel articles too, but I only had room (and energy) to list five. You can help out by listing your favourite in the comments.
Copy PivotTable Style Formatting
When PivotTable Styles were introduced in Excel 2007, it seemed that we couldn’t copy the Style’s formatting, when pasting a pivot table as values. Fortunately, John Walkenbach found a workaround for the problem, and shared the solution with the Excel world. We’re eternally grateful. You can read the steps in his article: Unlinking a Pivot Table From Its Source Data.
Vertical Bullet Graphs
Jon Peltier always has some new and awe-inspiring chart trick to show us. In How to Make Vertical Bullet Graphs in Excel, he details the steps for creating these charts that take very little room, but are packed with information. Jon plans to post instructions for a Horizontal version too, so subscribe to his RSS feed, if you haven’t already.
Create Your Own Add-Ins
Dick Kusleika tells us how he uses Excel to solve problems on the job. His blog posts inspire lots of discussion, and his post on moving from the Personal.xls workbook to personal add-ins was one of my favourites. Dick’s sample code was great, and there are many suggestions in the comments too, so be sure to read those.
Format Pivot Table Numbers Like Source Data
Along with delicious bacon recipes, Mike Alexander posts tasty Excel and Access tips on his Bacon Bits Blog. I liked his code for copying the number formats from the source data, and applying it to the pivot table data. You can read the details here: Auto Format PivotTables to Match Source Data.
Moving Forward with PowerPivot
If you’ve upgraded to Excel 2010, you’ve probably experimented with Microsoft’s free PowerPivot add-in. None of my clients have upgraded yet, so I enjoyed reading Ken Puls’ article on his experiments with PowerPivot. He describes the good and the bad, and his enthusiasm is contagious. Read Ken’s story here: Quantum shifts with PowerPivot.
Your Top Excel Tips
What were your favourite Excel blog articles from the past 12 months? What did you like about them? Did they inspire you to try new things, or help you save time?
Thanks for sharing!
Yesterday, I started out with the best of intentions, planning to get some work done, and find a couple of topics for upcoming blog posts. Then, while sipping my morning coffee and reading the RSS feeds, I clicked on an article about pivot tables. There were a few lines of sample code for creating a list of fields in the pivot table, which seemed like a good idea, but needed improvement. And there went the day!
But, in the end, I had some useful code for removing calculated fields from the pivot table layout, so the day was productive, but not in the way that I anticipated.
Strange Results for Orientation Property
The journey started out with that simple list of pivot table field names. I wanted to see more information about each field, so I wrote some Excel VBA code to list the pivot field caption, source name, location (orientation), position, sample data, and a formula (calculated fields).
Everything worked well, but the Orientation property gave some strange results for the fields in the Values area. Instead of listing them as Data, they were shown as Hidden.
Check the Data Fields
Next, I tried a different approach, looping through each type of pivot field separately, e.g. RowFields, DataFields. In that list, the orientation was correct, but no sample items were shown for the data fields, and the calculated field formulas weren’t listed.
Removing the Data Fields
Going back to the first code example, I played with the Orientation property in the code, but had no success in getting it to display correctly when looping through the PivotFields. When I tried to change the Orientation for the data fields, things got even stranger. I could change the regular data fields to xlHidden, to remove them from the pivot table layout, but those calculated fields wouldn’t budge.
Instead of changing the calculated field Orientation, Excel displayed the error message “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”
You can manually uncheck the calculated field boxes, and remove them from the pivot table, then check the box again, to put it back into the layout. However, if you record code while removing the calculated field, that recorded code shows the same error message when you try to run it.
Remove the Calculated Fields
A Google search showed that many other people had encountered this problem, and the only solution seemed to be to delete the calculated field, instead of trying to remove it from the layout. The Google search turned up a line of code to delete the offending calculated field, but that wouldn’t be much help if you wanted to keep the calculated field in your pivot table, for use later.
So, I wrote the following code that deletes each calculated field, then immediately adds it back to the pivot table field list, but not into the pivot table layout. If you’ve been having the same trouble with calculated fields, I hope this helps!
NOTE: See the updated code in the next section, to hide the calculated fields.
Sub RemoveCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim pfNew As PivotField Dim strSource As String Dim strFormula As String Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.CalculatedFields strSource = pf.SourceName strFormula = pf.Formula pf.Delete Set pfNew = pt.CalculatedFields.Add(strSource, strFormula) Next pf End Sub
Hide Calculated Fields
[Update 2016-05-10] The code above removes the calculated field from the pivot cache, then adds it again. That works okay if only one pivot table is based on the pivot cache. However, it’s a problem if there are multiple pivot tables based on that cache. The calculated field is removed from all of them, and only added back to the layout of the active pivot table.
There is a video that explains how this code works on this blog post – Macro to remove calculated fields
Thanks to Rory Archibald, who suggested the changing the Visible property instead.
With ActiveSheet.PivotTables(1).DataFields(“Sum of NewTax”) .Parent.PivotItems(.Name).Visible = False End With
Here is the code, that will hide all the Calculated Fields from the pivot table for the active cell:
Sub RemoveCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim df As PivotField On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Select a pivot table cell" Exit Sub End If For Each pf In pt.CalculatedFields For Each df In pt.DataFields If df.SourceName = pf.Name Then With df .Parent.PivotItems(.Name) _ .Visible = False End With Exit For End If Next df Next pf End Sub
You can use the PowerPivot add-in for Excel 2010 to create a report from multiple Excel workbooks or worksheets, by joining the tables using the Primary and the Foreign key, such as ‘ProductID’ in a Sales table and a Pricing table.
In this example though, we want to combine the data in two Excel files that have an identical structure — sales data for the East and West regions. In this case, we can’t use a key to connect the tables; instead, we want to create one combined table from all the data. The following technique allows you to import more than a million records from Excel, despite the fact that one worksheet can only contain up to 1,048,576 rows. At least that’s possible in theory — on my computer it imported about 1.2 million, then gave up, after whining about memory resources.
Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill’s work in last week’s posts on Combining Data from Two Excel Files in a Pivot Table.
Create a Connection in the Workbook
The key to this technique is to start by creating a workbook connection, before you launch PowerPivot.
- On the Excel Ribbon’s Data tab, click Connections.
- In the Workbook Connections window, click Add
- At the bottom of the Existing Connections window, click Browse for More.
- Navigate to the folder where your files are located.
- Select one of the files that you want to import — EastSales.xlsx in this example — and click Open.
- Select a table to import, and click OK.
- The new connection appears in the Workbook Connections window.
Combine the Data in PowerPivot
- Close the Workbook Connections window, and on the Ribbon, click the PowerPivot tab.
- Click PowerPivot Window, to launch the PowerPivot add-in.
- On the Table menu, click Existing Connections, or, on the Ribbon, click Design, then Existing Connections.
- At the bottom of the Existing Connections window, under Workbook Connections, click on the connection that you added, and click Open.
- In the Table Import Wizard, click Next, then select the table, and click Finish
- After the data is successfully imported, click Close.
Change the SQL Statement
Now that the first table has been imported, you can change its properties, to combine it with data from the second table.
- On the Table menu, click Table Properties, or on the Ribbon, click the Design tab, then click Table Properties.
- At the right, from the Switch To drop down list, select Query Editor.
- Edit the SQL statement, to create a union query, combining the two tables. In this example, the SQL statement is:
SELECT [EastSales$].* FROM [EastSales$] UNION ALL SELECT * FROM ‘C:\_TESTWestSales.xlsx’.[WestSales$]
After you change the SQL statement, click the Validate button, to verify that the statement is correct, then click Save.
Note: The SQL query string can also be edited in the Excel workbook connection window, by selecting the connection, and clicking Properties. However, there’s no Validate feature there.
Create the Pivot Table
Next, you can create a pivot table from the combined data.
- On the Toolbar, click the Create a PivotTable button, or on the Ribbon, click the Home tab, then click PivotTable.
- Select a location for the pivot table, and click OK.
- Add fields to the pivot table layout, to see a summary of the data.
Here’s the pivot table that was created from the combined data, with columns for the East and West regions. The Report Layout is Tabular, and Number format is used, with thousands separator and zero decimals.
Detailed Instruction and Sample Files
To see detailed instructions for this technique, with more screen shots, visit the PowerPivot from Identical Structure Excel Files page on the Contextures website. That page also has a link for downloading the East and West sales data that I used in this example.
Watch the PowerPivot Video
To see the steps for combining data from multiple tables in PowerPivot, please watch this PowerPivot from Identical Excel Files video tutorial.
Download the PowerPivot Add-In
You can download the free PowerPivot add-in from the Microsoft website: PowerPivot Download
Have you tried Microsoft PowerPivot for Excel 2010 (formerly Gemini)? It’s a powerful data analysis add-in for Excel, and is part of the Office 2010 Beta. If you haven’t downloaded the Beta, you can test PowerPivot in the hands-on Virtual Lab.
That’s where I tested PowerPivot last weekend, and hit a few snags, but was impressed by what PowerPivot can do.
On Friday, a surprise package arrived in my mailbox – a set of power tools! It was a promotion for last week’s release of PowerPivot, and the power tools had clever labels, like this one on the flashlight.
Did the power tools influence my decision to try PowerPivot? Of course! Testing PowerPivot was on my To Do list, and the power tools inspired me to move it to the top. Will the gift influence my testing? Nope. I’ll still tell you exactly what I think.
The PowerPivot Add-In
I had trouble with the virtual lab on my desktop computer, and couldn’t get the ActiveX control installed. My laptop is newer, and everything went smoothly there. Both machines are Windows XP, and I used Internet Explorer 8 as the browser.
Once the virtual lab was running, it was easy to get started, and work with PowerPivot in Excel. The PowerPivot add-in creates a new tab on the Excel Ribbon.
Click PowerPivot Window, to launch the add-in, and open the PowerPivot client window. From there, you can connect to data from a variety of sources. I’d normally connect to Access data, but in this example I used the SQL Server connection.
Next, select a table from the data source, and PowerPivot can automatically select related tables. You can also filter the selected data, before importing it. In the virtual lab, I connected to a Sales table that had almost 4 million records, and it took just a couple of minutes to import.
The Imported Data
In the PowerPivot client window, each table is on a separate tab. You can change the tab names, and add calculated fields in the tables. The formula bar looks just like Excel’s, and the field names appear automatically when you start typing.
Create a PivotTable and PivotChart
You can create a pivot table and pivot chart from the data, using the PowerPivot Task Pane (called the Gemini Task Pane in the virtual lab). The pivot table and pivot chart weren’t connected though – adding fields to one, didn’t affect the other. I’m not sure if that was a bug in the virtual lab, or a Beta feature that will be fixed later.
You can also add horizontal and vertical Slicers to the pivot table and pivot chart, to filter the data that’s displayed.
Try PowerPivot Yourself
This was just a quick overview of the PowerPivot test in the PowerPivot virtual lab. If you don’t have the Office 2010 Beta installed, I’d recommend this as a great way to see what PowerPivot can do.
It took me about an hour to go through the 3 modules, while making notes and taking screenshots. There’s a button to download a PDF file with the instructions, but that didn’t work, so I copied the instructions and pasted them into Word.
It was easier to read the instructions in Word, where I could increase the Zoom level. Also, the instructions disappeared at one point, and I would have had to start over, if I hadn’t made a copy.
The virtual machine hung on me a couple of times, and I don’t see a way to start anywhere except the beginning. Restarting was annoying, but it was pretty quick to go through the steps the second time.
For more information on PowerPivot, see the PowerPivot Team blog.
For more information on Excel Pivot Tables and Excel Pivot Charts, see the Pivot Table FAQs on my Contextures website.