![How To Remove Hyphens From Ssn In Excel For Mac Without Linking To Original Date How To Remove Hyphens From Ssn In Excel For Mac Without Linking To Original Date](/uploads/1/2/5/5/125507379/855888035.png)
Excel Pivot Tables are probably the single most powerful built-in feature of Excel. If you analyse lots of data, my guess is that Pivot Tables are one of your favorite tools. They are fast and flexible – they save you lots of time by allowing you to explore data.
I have a spreadsheet that imported from a personnel system. I need to report some aspects of it to another organization. The SSNs imported with dashes, and I need to report them without dashes.
I often find that checking out data with Pivot Tables gives me faster understanding of the data than using Excel formulas alone. But Pivot Tables have some drawbacks. One of those drawbacks is that your carefully chosen formatting is often lost when you change or refresh a Pivot Table. In this article we’ll have a look at how to stop Excel from resetting a custom number format in the Pivot Tables value area.
Reader Question: How do I stop Excel from resetting my custom number format when I update my Pivot Table? This week we take a look at a question asked by one of our readers – Joanne. Joanne uses pivot tables and wants to choose a custom number format for her value field. But the problem is that every time she adds a different value field, she loses her custom number format because Excel resets the number format to General. Let’s take a look at some screenshots. I put together some fictional Fruit Sales data and created a simple Pivot Table of the Order value in $USD for each salesperson by year.
You can download a sample spreadsheet by Custom Number Formatting This is an image showing the custom number format I used in the spreadsheet. The selected range shows the Pivot Table values area Here are the steps that most people use when they want to change the number format for the Pivot Table values area.
Right click on a number in the values area. Select Value Field Settings from the pop-up menu.
Click on the Number Format button. Select the desired Number Format (e.g. Number, currency, accounting, custom) Part 2 – How to Format the Pivot Table values area to a Custom Number Format (a more permanent way) I put together a video to show how we can format the values area so that our custom number format does not get reset every time we add or remove fields. Make sure you check the 'Preserve cell formatting on update' option in PivotTable Options. Select the whole Pivot Table values area first (not just one cell – for a good way to do this, see below “How to select the Values Area when you have lots and lots of values”). Bring up the Format Cells Dialog Box with Ctrl + 1.
Choose the desired Number Format (e.g. Currency). You may need to resize the columns to fit cell contents with ALT + H + O + I. And remember to check that you have your Pivot Table options set to Preserve Cell Formatting on Update (see video at 3 min 22 sec) How to select the values area when you have lots and lots of values Video: 4 min 38 sec. Use the Pivot Table Selection Tool to select (1) Entire Pivot Table, (2) Values Area. Select the Pivot Table by clicking on it.
In the Excel Ribbon go to PivotTable Tools Options Actions Select Entire PivotTable. Then click on Actions Select Values.
This will select the values area immediately After selecting the values area you can proceed to apply the desired Custom Number format. Download a copy of the Fruit Sales 2010 – 2012 spreadsheet All the data in the video and example screenshots above are taken from this spreadsheet. You can download a copy. Click the Fruits to download the sample Pivot Table file! The data is completely fictional and you get a free digital helping of Summer Fruits (Image courtesy of ) And make sure you enter our survey for a chance to win $50 of Amazon gift vouchers.
Find out more on our Part 3 – How to Format the Pivot Table values area to a Custom Number Format (the VBA way) Finally let’s have a look at how we can program Excel to instantly change the number format in our Pivot Table Value Area. I’m going to cover this last because VBA and macros are a topic that some people find too difficult. If that’s you don’t worry you can skip this bit. Don’t forget to check out the rest of my site – in particular you should take a look at our and our OK – let’s have a look at some VBA code. Code Snippet 1: Setting default format for all the pivot table fields Here’s some code I found on the Microsoft Developer Network (MSDN). It sets all fields in the Pivot Table to have a default custom number format. Sub UpdatePTFormat0 For Each pt In ActiveSheet.PivotTables For Each pField In pt.DataFields pField.NumberFormat = '(.
#,##0.00);(. (#,##0.00);(.)' Debug.Print pField Next pField Next pt End Sub But the problem is that when you add or remove a field from the values area the formatting is reset to the General format. So you need to run the code every time you change fields, which sure is better than doing it manually.
But can we do even better? Code Snippet 2: Use the PivotSelect Method to set Number Format in Pivot Table I played around with recording a macro to follow my steps in the video (see Part 2 above) and discovered the PivotSelect Method. This functionality was added in Excel 2007 and allows you to select parts of the Pivot Table by name (e.g. XlBlanks, xlDataOnly, xlDataAndLabel). You can look up the for more detail on using the method. When you use the PivotSelect method in Excel 2010 in the way below, Excel remembers the format even when you change the field(s) in the values area.
Sub UpdatePTFormat For Each pt In ActiveSheet.PivotTables pt.PivotSelect ', xlDataOnly, True Selection.NumberFormat = '(. #,##0.00);(. (#,##0.00);(.)' Next pt End Sub If you are familiar with VBA I suggest you try out my code. It should work in both Excel 2007 and 2010. You may want to assign this macro to a keyboard shortcut (such as Ctrl + Shift + Q).
Subscribe to our email newsletter If you like Excel tips & tricks why not subscribe to our free weekly newsletter, which you can do by filling out the form below the sharing buttons. And remember to share this article with your friends and colleagues using the sharing buttons.
Cheers – Victor. Thanks for your comment and question. I don’t think what you are trying to do is simple (though I may be wrong). You can try applying a custom number format, disabling the auto-format on refresh from pivot table options. Though this might not work if the pivot table expands with new values for count & sum. Another way to get around this problem might be to build a reporting table formatted the way you want, and to use the GETPIVOTDATA function to extract data from the pivot table.
This approach gives you more control over format of the reporting table, as it is separated from the format of the pivot table. Sounds like you have a nicely formatted worksheet. My advice is to look into using the GETPIVOTDATA function, because you can use this to read pivot data into a separate worksheet that retains its formatting. Here is a link to Microsoft’s help on that function: Pivot tables are great for quickly playing around with data, but because they usually lose formatting on refresh it’s a good idea to learn about GETPIVOTDATA and have a worksheet separated from the pivot table, that is used exclusively for presenting your data. Cheers Victor.