Setting Default PivotTable settings in Excel

Microsoft’s latest update to Excel now allows you to set defaults for how you want your PivotTables formatted. As someone who builds PivotTables all the time & 98% of the time want them setup the same way – this is a huge help!

To access the default options, open Excel and go to: File > Options > Data > & click on the Edit Default Layout button.

If you have an existing PivotTable that’s setup the way you want, simply use the Layout Import option at the top of the window to select the existing PivotTable and import the options all ready set.

The options I have set are:

• Subtotals: Do Not Show Subtotals
• Grand Totals: On for Rows and Columns
• Report Layout: Show in Tabular Form
• repeat All Item Labels: Checked box

In addition, click the PivotTable Options button and you can optimize all your PivotTables moving forward (huge file size saver!).

Select the Data tab and select/unselect the following options:

• Save source data with file: Unchecked
• Enable show details: Checked
• Refresh data when opening the file: Checked
• Number of items to retain per field: Select the None option

This will force excel to refresh the PivotTable every time the file is opened but it will also not save the source data (calculations behind the PivotTable) which makes the file size much smaller & less susceptible to file corruption.

Enjoy!
~Yosef

Building Dynamically Driven Excel Hyperlinks

I recently came upon a challenge that at face value looked straight forward but actually proved to be quite tricky.

What I wanted to do was create a drop-down menu in Excel. Depending on the choice selected, a hyperlink would be created that would allow you to “jump” to a specific location in the Excel workbook based upon your selection.

The solution I hit upon is to use the Hyperlink formula with a unique twist.

First, I created two different worksheets – for this example I’ve creatively named them “Test Sheet 1” & “Test Sheet 2”.

Next, I created a drop-down list menu using the two worksheet names (cell C2 in picture below; see my previous post for details: Excel: Creating a Drop Down Menu).

Excel: Drop Down Menu Example

Finally, in cell C4 I put the following formula =HYPERLINK("#'"&C2&"'!A1","Go to "&C2) where the first part "#'"&C2"'!A1" builds the actual link #’Test Sheet 1′!A1 or #’Test Sheet 2′!A1 (depending on the drop down selection) that takes you to the A1 cell on either the Test Sheet 1 or Test Sheet 2 worksheet, and the second half "Go to "&C2 builds the text that’s displayed to the user in cell C4.

Excel: Dynamic Hyperlink Formula

Viola! Depending on the drop down item selected, the hyperlink will dynamically update and direct the user to the correct worksheet.

The tricky part was in figuring out where to put all the single & double quote marks as well as learning about using the hash mark to indicate that the URL was inside the current workbook without having to specify the full name of the workbook.

This should come in handy when building table of contents, forms, &/or surveys in Excel.

~Yosef

Excel PivotTable Slicer Options Explained

Microsoft has a great tutorial on how to use slicers to filter PivotTable data and I don’t feel that I need to duplicate their tutorial here. You can read all about it here: Microsoft: Use slicers to filter PivotTable data

What the article does not cover is the Slicer settings available by right-clicking on a slicer and selecting that bottom option “Slicer Settings”.

There are 3 main settings that you can set here – I typically only change the last one and leave the rest on their default settings. They are:

1. Name – this is the name by which you can refer to the slicer in formulas. I typically just let this be the default. (e.g. CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()))
2. Header – this is the name displayed above the slicer. You can choose to turn it on or off and customize the name if needed. Again, I typically just leave the header visible with the default name.
3. Item Sorting and Filtering – Here’s where you can choose to:
• order the values ascending or descending
• Perhaps most important for visual aesthetics (and the value I typically choose to modify most often) you may hide items with no data
• Choose to visually indicate items with no data (by making them more transparent)
• Choose to show items, which have no data associated with them, last in the list
• Continue to show items which have been deleted from the original data source table

Questions? Suggestions? Leave a comment!

Help! Why won’t my Excel formula calculate?!

Note: These instructions are based off Excel 2010 but are applicable for Excel 2007 through Excel 2013 (Office 365).

Has someone sent you a spreadsheet or perhaps you’re working on one and all of a sudden your formulas look like formulas and won’t calculate? Here are the top solutions I have come across for fixing this common error.

1. Cell text formatting is set to “Text” – If the formatting of the cell has been set to Text instead of General or some other format, the cell will not calculate because it assumes that anything in the cell is text and not a formula. Change the formatting of the cell by pulling down on the drop down in the Number section on the Home tab of the menu ribbon





2. Show Formulas (Ctrl + ~) has been selected / pressed – If the Show Formulas option has been selected, calculations will show their full formula and not show the calculated results. To toggle back and forth, you can either use the hotkey combo Ctrl + ~ or select/deselect the Show Formulas option in the Formula Auditing section (I highly recommend learning to use all tools in this section when troubleshooting Excel problems!) on the Formulas tab of the menu ribbon.



3. An apostrophe (‘) has been placed before the equals (=) sign – Typically someone will have done this on purpose, however placing an apostrophe before a formula will make the cell mimic the first solution listed above where the cell formatting is set to Text. Remove the apostrophe to force the cell to calculate.



4. Automatic calculation of the worksheet / cell has been turned off – If you’ve been sent an Excel sheet that’s very large, sometimes people will turn off automatic calculations so that the spreadsheet doesn’t break your computer upon opening it. To turn on or off automatic calculations within your worksheet, click on the pull down menu of the Calculation Options in the Calculation section of the Formulas tab on the ribbon bar. Alternatively, this setting can be reached by going to the File tab, selecting Options, going to the Formulas tab and then setting the Calculations options there.

Hope this helps when you get stuck! I would appreciate hearing if anyone has come across other solutions to this common complaint?

~Yosef

Excel: How to use the INDIRECT function – Example

I received a request for a follow-up post to my last post on how to use the INDIRECT function in Excel. The request was to create a sample table showing a potential setup and use of it.

The best use I’ve found for the INDIRECT function is to build dashboards. You can have all your data stored on one sheet and then use INDIRECT formulas in combination with others to pull over only the data you want in a quick and easy manner.

In the picture below, there are two sample tables. The one on the top left is my “Original Data Table” which could be stored on any tab. The “INDIRECT Table” on the right uses INDIRECT formulas to pull over the data from the first, original table.

Remember that the INDIRECT formula takes in a text string and converts it to a cell reference. In this case, I put my column references as table headers and my row references as row IDs for my INDIRECT table. In this second picture, you can see how I combine the column reference & row reference in my INDIRECT formula to create a formula that is easily copied over to all the other cells in my table.

With properly setup column & row references, I need only write a single INDIRECT formula to pull over all my data in the correct order. I could just have easily made it skip every other row or skip a couple of columns, or even reorder the columns or rows by reordering my column & row references. My INDIRECT formula wouldn’t have to change at all!

Hope this helps! Let me know if you have any further questions.

Thanks!
~Yosef

Excel: How to use the INDIRECT function

The Excel INDIRECT function is useful for displaying data in another cell by passing it text strings to tell it where the value is.

The syntax for the Indirect function is as follows:

INDIRECT(ref_text,[a1])

Where:

ref_text is a text reference to another cell & [a1] is a logical value that specifies what type of reference style ref_text is using.

In excel you have two types of cell reference styles:

1. A1 style is where each row has a number and each column has a letter (the default view in excel).
2. R1C1 style is where each row has a number and each column has a number.

A1 is indicated by [a1] = “TRUE”, R1C1 is indicated by [a1] = “FALSE” in the INDIRECT formula.

For example in order to display a specific column of data in a given row (in this example, row 7), the following function is used:

=INDIRECT($B$1&$A7,TRUE)

The first argument, $B$1&$A7 concatenates the values in cell B1 & A7 together to create a text statement of where the data that needs to be displayed is stored. In this case B1 = “Data!B”, A7 = 3 so the final result is “Data!B3” so the value displayed in the cell is the value on the Data tab in the B3 cell.

Column A now displays the row of the data for that particular project on the “Data” tab.

Hope you find this useful!

~Yosef

Excel: Formula Auditing – how to troubleshoot #N/A or #REF errors?

Excel comes with a handy tool called the “Formula Auditing tool” located on the Ribbon under the “Formulas” section under “Formula Auditing”.

(Click to Enlarge)

1. The Trace Precedents tool shows you visually which cells are used by the selected cell in it’s formula.
2. The Trace Dependents tool shows you visually which cells are used by the selected cell in it’s formula.
3. The Evaluate Formula tool walks you through the formula in the selected cell so you can see how it’s calculating the formula. If there is an error, this is a handy way of figuring out where the calculation goes wrong so you can potentially fix it.

(Click to Enlarge)

Simple right? If not – feel free to leave a comment below & I’ll try to help!

~Yosef

How to Hide Pivot Chart Filters

(Directions below are for Excel 2010)

I recently built a simple dashboard using pivot tables with corresponding pivot charts. I was asked by my client if I could remove the filters from the pivot charts so that they would have a cleaner look for presenting. I had never worried about this before and didn’t know how off the top of my head so I did a couple of quick Google searches & couldn’t find any references on how to hide the filters! I was very surprised that I couldn’t find anyone else asking the question so I thought I would share how to easily do this. Luckily it’s pretty simple – I just didn’t realize these options existed!

Here’s a Pivot chart with the usual filters visible:

To remove these filters, click the “Field Buttons” (or drop down for more control) button on the Analyze tab of the PivotChart Tools section of the menu ribbon (only visible if the chart is selected):

You can then choose which (if any) filters to display so that your chart looks nice & clean:

Hope this helps!

~Yosef B.

Excel: Creating a Drop Down Menu

Ever wondered how to add a drop down menu to your Excel file? Here’s an easy way to add one wherever you want!

To create a drop down menu, you have two options. First, you can create a list of all the items you want to display in your Drop Down List. E.g. “Yes, No, Maybe” or “Early, Late, None”, etc. in a range of cells (one option per cell). Alternatively, simply copy a list of items, ensuring that they are comma delimited.
Once you have your items listed:

1. Select the Data Tab from the Excel Menu Ribbon. In the “Data Tools” section, click on “Data Validation”.
2. Under the Settings Tab, select “List” from the “Allow” drop down menu.



(Click to enlarge)

3. Using the arrow on the right hand side of the “Source” box, select the list of items that you want to display in your Drop Down List.
4. Click “OK”

(Click to enlarge)

Got questions? Leave a comment below!

~Yosef

Excel: Conditional Formatting

Excel provides the ability to automatically apply specified formatting to a cell based upon the value in the cell or by comparing the value of the cell vs. the value in another cell.

Here are instructions to apply basic conditional formatting:

1. Select the first cell which you wish to have automatically highlighted based on a value in another cell.
2. Select the Home Tab on the Ribbon. In the “Styles” section select “Conditional Formatting”. In the drop down menu select “New Rule…”.



3. Select “Use a formula to determine which cells to format”
4. In the “Format values where this formula is true:” box type in a formula that describes the values in the cell the highlighted cell is based on. For example, if you want cell A1 to change to green when cell B1 contains the value “1” type “=if(B1=1,TRUE,FALSE)”. This formula will return “TRUE” if B1 contains a 1, otherwise it will return “FALSE”. Your cell will only change color if the returned value of the function is “TRUE”.
5. You can overlay multiple formatting rules on the same cell – therefore you can have the cell turn one color based on one rule and another color based off another rule. You do need to be careful that the formulas will not both be true at the same time otherwise your conditional formatting will not work.
• Note: In addition to an equality, you can also use any other comparative logic symbol. Examples include: “Does not equal” e.g. =if(A1B1,TRUE,FALSE) or “Is greater than” e.g. =if(A1>B1,TRUE,FALSE).
6. Select the “Format…” button and apply whatever formatting you want to occur when the formula returns “TRUE”.



7. Click “OK”
8. To apply your formatting to multiple cells, select the “Format Painter” tool and copy your formatting to other cells.

Pretty straight forward but if you have questions, as always leave a comment & I’ll get back to you!

~Yosef