Excel tips and tutorials
If you’re setting up a big pivot table, it’s easy to lose track of what you’ve added, and what filters have been applied. To help you stay organized, I’ve created a macro to list all pivot fields and pivot items in the selected pivot table’s row, column and filter areas. You can download the sample file, and test the macro in your own files.
Continue reading “Macro to List All Pivot Fields and Pivot Items”
You don’t have to stick with the default formats for your pivot tables. You can create a pivot table style with your own colours, and other formatting options that you like. Here’s how to get started, and a video with a simple formatting change that you can make.
Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”.
Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.
Continue reading “Macro to Remove Pivot Table Calculated Fields”
On Chandoo’s blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week’s list, but my favourite one was down in the comments section – how to enter complex Excel formulas fast.
I’ve been using Excel for a long time, and I’ve never seen this suggestion before. And it’s a real time saver!
Mike Alexander, from the Bacon Bits Excel blog, has just released a major update to his Dashboard Tools add-in. And the great news is that you can get a free copy for the next few days! Mike is making this available until May 7th, at midnight (Central Time Zone).
If you’re analyzing sales data from year to year, you can make a line chart that shows each month’s sales. That lets you see if there were any months with big differences, and shows how sales went up and down over the year. Another option is to use a Running Total to compare years in Excel. It’s quick and easy to set up with a pivot table and pivot chart.
Continue reading “Use Running Total to Compare Years in Excel”
Do you need a quick way to show different data in a worksheet? For example, on an order form, you could let people select a region, and automatically include the shipping cost or tax rate for the selected region. I added an English/French selector to a workbook last week, so a formula would work correctly, in either language.
In a big Excel file, it’s handy to have hyperlinks that take you from one sheet to another. Maybe you need a table of contents, or a single link on a summary sheet that takes you to a data entry sheet. You can use the Insert Hyperlink command on the Ribbon, but it’s even quicker to create an Excel hyperlink with drag and drop. Here are a couple of ways to do that.
And there are a couple of Excel announcements too.
Continue reading “Create an Excel Hyperlink With Drag and Drop”
It’s pivot table time! First, we’ll take a look two common problems with time values in pivot tables. Then I’ll show you a couple of ways to save time when working with pivot tables.
In most cases, it’s best if you keep people away from your data in Excel. It’s too easy to mess things up! Instead, set up a worksheet where they can use Slicers to set filter criteria in Excel.
Then click a button, and a macro dumps the data onto the worksheet. In the screen shot below, you can see how it works – easy, quick, and safe!
Continue reading “Use Slicers to Set Filter Criteria in Excel”
