K2's Road to Excellence - Part 8
|
More Ways to
|
Questions & Answers from the Webinar
Excel Questions |
Answers |
For a pivottable, when I refresh it resets my formats ... how do I get them to stay? |
To keep the cell formatting when you refresh your pivot table make the following changes in the PivotTable options dialogue:
|
How do you get the same number of decimal places on all the cells in a pivottable, without clicking on value field settings and selecting for each of the values; this takes so much time. |
Unfortunately, Excel does not have a method to format all value field settings at once. You can use a macro to accomplish the task or use an add-in for Excel. |
I use Excel 2010. In PivotTable data, will Excel 2010 treat a "blank" field the same as a field with 0 (numeric zero) data? |
A blank cell is considered text resulting in the count function when the field is added to values. You can change the calculation to sum for example manually and the calculation will be correct. |
How do we group dates by week? |
Select any cell in the Date Row or Column
Alternatively you can create a helper column in your data to group by week. |
How do we change PivotTable dates to fiscal periods instead of calendar period? |
I will be showing you how to do that in the next mini-webinar on May 22nd. |
How can we make formatting of a pivottable persist? It seems that pivottables always revert to crummy default formats. |
See above |
Adagio Questions |
Answers |
I want to refresh a pivottable each month without having to rebuild it. Adagio seems to export only to a new excel sheet. can it "refresh" an existing sheet with a pivottable? |
Learn about Excel's "Get and transform". This tool allows you to automatically combine multiple data files into a single table/pivot table. So, you would export the data from Adagio every month and put the resulting table in a Folder. Then open your Excel spreadsheet with the "Get and transform" connection and the additional table will be added into your data. |