Change Pivot Table Filter All Sheets or Active Sheet

In Excel 2010, you can use Slicers to change the filters in several pivot tables, with a single click.

ExcelSlicerDetail01

If you don’t have Excel 2010, or don’t want to use Slicers, you can use programming to change multiple pivot table filters with a single click.

Yes, it’s more work than adding a Slicer, but better than manually changing all those pivot tables!

Change All Pivot Tables

Last December, I described how to add code to your workbook, so if you changed one pivot table filter, all the other pivot tables in the workbook would change too.

Click here to read that article, and the comments: Change All Pivot Tables With One Selection

In those comments, people asked how to modify the code, so only the pivot tables on the active sheet were affected, or only a specific field was changed.

In response to those comments, I’ve created a new version of the sample file.

Change All Pivot Tables or Active Sheet Only

The latest sample file for changing pivot table fields has 3 variations on the “Change All Page Fields” code.

It also changes the “Multiple Item Selection” settings to match changed page fields (Excel 2007 and Excel 2010 only).

The three variations are:

  1. Change any page field in a pivot table, and all matching page fields, on all sheets, are changed.
  2. Change any page field in a pivot table, and all matching page fields, on the active sheet only, are changed.
  3. Change a specific page field in a pivot table, and that page field, on the active sheet only, is changed.

Download the Sample File

To see the code, and try the variations, you can download the sample file from the Contextures website. The file will work in Excel 2007 or Excel 2010, if you enable macros.

PT0027 – Change All Page Fields – All Sheets or Active Sheet

You can also download the other sample files, showing how to change a specific field, or all fields, in the workbook’s pivot tables.

PT0008 – Change Multiple Page Fields

PT0015 – Change Multiple Different Page Fields

PT0016 – Change Page Fields With Cell Dropdown

PT0021 – Change All Page Fields

PT0025 – Change All Page Fields with Multiple Selection Settings

______________

Show Excel Chart or Data in Dashboard With No Macros

In this Excel dashboard example, you can select “Chart” or “Chart Data” from a drop down list. Magically, with no macros in the workbook, the selected item appears on the worksheet.

select "Chart" or "Chart Data" from a drop down list
select “Chart” or “Chart Data” from a drop down list

With this technique, you can store your data and chart on a hidden sheet in the workbook, where no one can mess with the numbers. (Not that anyone would!)

Continue reading “Show Excel Chart or Data in Dashboard With No Macros”

Copy PivotTable Style

Yesterday, i created a custom PivotTable Style for a customer, to make it easy to format multiple pivot tables, using their corporate colour scheme.

PivotTable Styles are available in Excel 2010 and Excel 2007, and if you don’t like the existing styles, you can create your own custom styles, and apply those to any pivot table.

Copy Custom PivotTable Styles

Unfortunately, there’s no built in way to copy a custom PivotTable style from one workbook to another.

A while ago, I made this video, to show you a workaround for copying your favourite styles to a different workbook.

Remove Existing Formatting

If you’re applying a built-in or a custom style to a pivot table, you might need to remove any manually applied formatting first.

  • Instead of clicking on the PivotTable Style icon, right-click on it.
  • Then, click Apply and Clear Formatting
Apply and Clear Formatting
Apply and Clear Formatting

You might need to tidy up the pivot table after you apply the new style, but with Custom Styles you can quickly format your pivot tables, so they have a consistent appearance.

______________________

Data Entry Shortcuts for Dates and Numbers

Are you working hard this week? Instead of doing all the work yourself, let Excel do some of the data entry for you. These quick tips show you how to enter a date or number series, with a minimum of effort.

Enter a Series of Numbers

With this shortcut, you can quickly create a series of numbers on an Excel worksheet, such as a series of even numbers or odd numbers.

  1. To start the series, type the first two numbers in adjacent cells.
  2. Then, drag the Fill Handle to continue the series on the worksheet, as far as you need it to go.

This Excel Quick Tips video shows you how to fill the series, and there are many more Excel Data Entry tips on the Contextures web site.

Enter a Series of Dates

With the next shortcut, you can create a series of dates on an Excel worksheet, incremented from your starting date.

  1. To start the series, type the first date in a cell.
  2. Then, drag the Fill Handle to continue the date series on the worksheet, as far as you need it to go.

This Excel Quick Tips video shows you how to fill the date series.

Create Excel List of Dates by Week

With the final shortcut, you can create a series of dates, by week, on an Excel worksheet, incremented from your starting date.

  1. To start the series, type the first date in a cell.
  2. Then, press the right mouse button while you drag the Fill Handle to continue the weekly date series on the worksheet, as far as you need it to go.
  3. Release the mouse button and click Series in the popup menu.
  4. Type a 7 as the Step value, and click OK.

This Excel Quick Tips video shows you how to fill the date series.

__________________

Create an Excel UserForm

This week, I’ve been working on a client’s Excel file, and we’re using a UserForm for data entry, instead of worksheet cells.

image

Data Entry and Storage

Data can be entered in the UserForm, and stored in a worksheet, when the form is closed.

The UserForm could open automatically when the file opens, or put a button on the worksheet, and click that to open the form.

On the Contextures website, you can find instructions and sample workbooks, for creating a simple UserForm, or a UserForm with drop down lists.

Watch the Excel UserForm Videos

To see the steps for creating an Excel UserForm, you can watch this 3-part Excel Video Tutorial series.

You’ll see how to add a UserForm to your Excel file, then put text boxes and buttons on the form.

Demo – Excel UserForm for Data Entry Demo

Creating a UserForm – Part 1

In part 1, you’ll see how to create a blank Userform. Then you’ll name the UserForm, and next you’ll add text boxes and labels.

Users will be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box

Creating a UserForm – Part 2

In Part 2, you’ll learn how to add buttons and a title on the UserForm.

With buttons on the UserForm, a user can click to make something happen.

For example, click a button after entering data in the text boxes, when you’re ready to move the data to the worksheet storage area

Creating a UserForm – Part 3

In Part 3, you’ll learn how to add VBA code to the controls, and you’ll see how to test the UserForm.

The VBA code runs when a specific event occurs, such as clicking a button, or entering a combo box. In this example, the user will click a button, and the VBA code will move the data to the worksheet storage area

Creating a UserForm – Part 4

In Part 4, you’ll see the code that adds the items to the combo boxes.

____________

Drill to Detail With Excel Slicer Filters

With Slicers in Excel 2010, you can easily filter several pivot tables with a single click. In the screen shot below, the Slicers are filtering the Severity and Priority fields in the pivot table.

However, there is a problem with Drill to Detail with Excel Slicer Filters, in some version.

Continue reading “Drill to Detail With Excel Slicer Filters”

How to Spot the Old Excel File

imageI’m going cross-eyed this week, working with an Excel workbook that is full of rating tables.

Last week, I converted the rating manual from PDF format to an Excel file.

Setting Up Formulas

Now I’m setting up the formulas, to pull the correct rating data for the selected criteria.

Many of the tables have changed in structure from the previous version, so there are lots of adjustments required in the workbook.

While I work on the new version of the Excel file, occasionally I need to check the previous version, to see how things were set up there.

Don’t Edit the Wrong File

The danger in having multiple copies of an Excel file open is that you might accidentally make changes to the old file, instead of the new one. Of course, that’s never happened to me, but a close friend had that problem once. 😉

But seriously, as you flip between files, and click on different sheets in those files, it is easy to forget where you are.

You change a few formulas, add items to a list, and save your work. And that’s when you realize the you spent time editing the old file. Sigh.

Spot the Old File

Today, while working with the old file and the new one, I wanted a foolproof way to know which file was active. First, I thought about adding fill colour to the first few rows of each worksheet in the old file.

That wouldn’t work too well though, because there was colour coding in some of those cells already.

Then it dawned on me – I could colour all the sheet tabs in the old file. The file didn’t use tab colouring, so that would make it easy to tell the files apart.

Change Tab Colour

To add the tab colour in the old Excel file, I did the following:

  • Right-click on any sheet tab, and click Select All Sheets
  • Right-click on one of the tabs, and click Tab Color.

TabColourOld00

  • Click on the colour that you’d like to use for your old file – I picked bright pink – then click OK.

TabColourOld01

  • Right-click on one of the sheet tabs, and click Ungroup Sheets.

TabColourOld02

Now, it’s certainly easy to see which file is the old one. Don’t make any changes in the file with the pink tabs!

TabColourOld03

__________

Convert PDF File to Excel

pdftoexcelconverter00 When I got back to my office after a recent vacation, there was an email from Una, about a PDF to Excel conversion service, that she asked me to try.

There was also a big pile of emails from family, friends and clients, with more urgent requests. So, Una’s email got dropped into the Follow Up folder, with little chance of being looked at again.

Until today. And I’m very glad that I read that email – it saved me a few hours of work, and that will save my client some money!

Avoiding Data Entry

Today I met with a client who needs an insurance quote calculator updated. In the new version, we need to add a long list of motorcycle makes and models.

They gave me a printed copy of the manual, and it has several pages of lists that we need to add in the update. I asked if they could get an Excel or Word version of the manual, so we could copy the data from there, into our calculator file.

Even a PDF file would be better than nothing, so I asked for that, as a third option. I figured that I could use Adobe Acrobat to extract the text somehow, if a PDF file was all that we could get.

And, of course, a PDF file was the only option available.

Convert a PDF File

When my client sent the PDF file, I remembered Una’s email, and dug it out of the Follow Up pile. Here’s what she said about the PDF to Excel conversion service that she is promoting.

It is a free online PDF to Excel conversion service – http://www.pdftoexcelconverter.net  .

There are only few similar tools around and this tool has following advantages over them:

  1. It has no significant file size limitations (documents of up to 30MB can be converted)
  2. It’s web based and it requires no installation on the computer
  3. The table data in the PDF will be accurately represented
  4. As stated in our Privacy Policy user documents and email addresses are 100% safe.

Hmmm…Free…and safe…and free.

So, I clicked the link to the website, and uploaded the motorcycle manual. For some reason, I had to click the Browse button twice, before it worked.

pdftoexcelconverter01

In Step 2, I entered my email address, and click the Send button.

pdftoexcelconverter05

The file was about 3 MB and uploaded in about one minute.

pdftoexcelconverter02

Less than a minute later, there was an email from the website, with a link where I could download my converted file.

Data Converted to Excel from a PDF File

The email told me to click on the link below, and I did.

pdftoexcelconverter04

However, I didn’t notice that the first link was within the “Advertisement” section, and it took me to a product page for Investintech. They showed some of their fine paid products, like Able2Extract Pro, which I have used in the past, to convert PDF files to Excel. I didn’t want to buy anything though – I wanted to download my file.

I went back to the email, and scrolled down a bit further. There was the download link, for my converted Excel file. Clicking that took me to a page with a download link.

The instructions say to click the link, but it should tell you to click the button – that link isn’t clickable.

pdftoexcelconverter03

The file downloaded quickly, and when it opened, I was very impressed with the results. The tables were in good shape, and the lists will be easy to copy and paste into our Motorcycle Calculator file. I won’t have to spend a few hours typing those lists in the Excel file, or trying to convince someone else to do it!

Desktop PDF to Excel Converter

The http://www.pdftoexcelconverter.net website states that your privacy will be protected, but if you’re trying to convert confidential documents, you can use a desktop PDF converter instead. You’ll sleep better at night, knowing that all your files are securely locked up, with no possibility of accidentally going astray.

As I mentioned, I’ve used Able2Extract Pro, and found it easy to use, with excellent conversion results. Unfortunately, I didn’t have a copy available today, but this file wasn’t confidential, so the online version was fine for this conversion.

Able2Extract Pro

Convert PDF to Word Failure

As a side note, I also tried converting the PDF file to Word, to see if the lists would be easier to copy and paste. I used the PDF to Word site, operated by Nitro, and the upload went well.

pdftoexcelconverter06

About 6 hours later, I got an email that said, “Sorry, an unexpected conversion failure occurred when converting your file.” Good thing that I wasn’t depending on that site for a quick solution to my problem!

_____________