Pivot tables and Sums

Hi Kajal

Thank you for your questions. Depending on your data set one way would be to replace the data on the source sheet and in the pivot table click the Select data button then highlight your new data.

If you wanted to keep your source data and add new information to it you could format your source data as a table (Home Ribbon > Styles group and click Format as Table)

Now when you add data at the bottom of your table it will be included in your data - click refresh on your Pivot Table > Options ribbon to bring the new values into your pivot table.

As with all new approaches try this on a backup of your live data before using on live or production documents.

I hope this helps - do let me know if you have any further questions.

Kind regards,
Andrew

Hi Kajal

I think the Null question was a conditional format to highlight cells that are empty. In fact I realise now there is function that is better suited to this.

The isblank() function tests if the value of a cell is empty or not. If it is the test will return TRUE. This can be combined with conditional formatting as follows:

Highlight the cells to be tested
From the Conditional formatting menu (Home > Cell Styles > Conditional formatting) choose new rule and select the option to create a formula based on a rule

In the formula box enter =isblank(
then click on the first cell in the range you highlighted, completing the formula with a close bracket. It will look something like =isblank($A$1)

Next the important part - remove both dollars in the formula.

Finally use the format button to decide what formatting should be applied if a cell is empty.
Click OK and OK again to apply

Now empty cells will be formatted and non-empty cells unchanged.

I hope this helps too - let me know if you have further questions.


Kind regards,
Andrew