Automatic Pivot Table Refresh

AUTOMATICALLY REFRESH PIVOT TABLE WHEN FILE IS OPENED

The below example explains how to automatically refresh a pivot table when the spreadsheet file is opened in MS Excel (with screenshots and step-by-step instructions). 

This is mainly helpful when you are linking with different excel file. i.e when the data source is from another excel file and you try to open this file with pivot table, it will automatically link that file and refresh the pivots instantly.   It is also quite useful, when you have multiple pivot tables in your excel file, at one shot it will refresh all the pivot tables and you need to do similar settings in all your pivot tables...  

Right-click on the pivot table and then select "Pivot Table Options" from the popup menu.

When the Pivot Table Options window appears, select the Data tab and check the checkbox called "Refresh data when opening the file". Click on the OK button. 

Now you are done with your automatic pivot refresh settings.

 

Happy Learning!!

 

Conditional Formatting

Highlight Cells Rules  

 

To highlight cells that are greater than a value, execute the following steps.  
1. Select the range A1:A10. 

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...  

3. Enter the value 80 and select a formatting style

4. Click OK.

Result. Excel highlights the cells that are greater than 80. 

5. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically. 

Note: you can also highlight cells that are less than a value, between a low and high value, etc.  

Clear Rules 

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10. 

2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

Top/Bottom Rules 

To highlight cells that are above the average of the cells, execute the following steps.

1. Select the range A1:A10. 

  

2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...  

 

3. Select a formatting style. 

4. Click OK.

Result. Excel calculates the average (42.5) and formats the cells that are above this average. 

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!

 

Happy Learning!!

Excel formulas and functions - Tips for Troubleshooting

Finding & fixing errors in excel formulas & functions can be tedious & time-consuming.  Here are some practical tips to help you cut to the chase.

Error values: Not a nuisance but are clues

Excel displays an error value, if it cannot calculate a formula. These values are important clues & knowing what they mean can help quickly spot errors. The following error values are your initial steps, & often the only steps, to debug the error:

1. #VALUE!: You've expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation. 
2. #REF!: You've referenced a cell incorrectly. Most likely, you've deleted a cell. For instance, the expression =A1+B1 won't return an error if you delete the value in B1. But if you delete column B, it will. 
3. #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small. 
4. #NULL!: You specified an intersection between two ranges that don't intersect, such as A1:C3 & D4:F6. If the ranges do intersect, you've probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6). 
5. #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can't recognize the text as any of those things, it displays this error value. Check your function names, cell references, & range names (make sure the range name actually exists if you've spelled it correctly). If all ranges are correct, make sure you've delimited text using quotation marks. 
6. #DIV/0: Excel can't divide by 0 & your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find & fix.

Happy Learning!!