K2's Road to Excellence - Part 8
Excel PivotTables, Part 1 and Adagio


This is the eighth in a series of sessions in Excel that will provide you with the information you need to become an Excel expert. In this session we turn our attention to PivotTables that are the most powerful feature of Excel, yet few accountants and bookkeepers use them in their day-to-day activities. For some, PivotTables are too intimidating; for others, PivotTables are a foreign concept. After this webinar you will be ready to begin using PivotTables to accomplish routine analysis and reporting.

Learning Objectives

  • Identify and define the six main elements of a PivotTable report
  • Create PivotTables that summarize large volumes of data
  • Drag-and-drop pivoting, group and ungrouping, and drilling down to underlying details

Adagio and Excel - Useful Integration in the Accounting Department
Excel is the most popular accounting module on the planet. The ease with which you can move data in and out of your accounting software will contribute significantly to your quality of life!

Find out the comprehensive integration with Excel that Adagio offers, including:
  • Template driven imports and exports for all master and transaction file
  • An ExcelDirect button that drops any grid directly into Excel as a formatted table
  • An ExcelDirect button in the financial reporter that can send a full set of financial statements to Excel as a single workbook
  • ExcelDirect Print buttons on all major module reports that send the most important reports to Excel already formatted as a table
  • Adagio GridView which allows the easy generation of custom inquiries into the accounting data
  • Adagio ODBC which allows industry standard tools to connect directly to the accounting data

Learning Objectives

  • Discuss the need to transfer data between the accounting and other software
  • Learn the benefits of Excel tables
  • See how Adagio allows easy integration with external systems

More Ways to
Earn Verified CPD


Access the many courses from the leader in providing CPD to professionals throughout North America.
Catalogue of upcoming webinars

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:

  1. Select any cell in your pivot table, and right click.
  2. Then choose PivotTable Options from the context menu.
  3. In the PivotTable Options dialog box, click Layout & Format tab, and then check Preserve cell formatting on update item under the Format section. (You may also want to clear the Autofit column widths on update)
  4. Click OK to close this dialog, and now, when you format your pivot table and refresh it, the formatting will not disappear.
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
  1. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection.
  2. In the Grouping dialogue box, select Days and deselect any other selected option(s). As soon as you do this, you would notice that the Number of Days option (at the bottom right) becomes available.
  3. There is no built-in option to group by weeks. You need to group by days and specify the number of days to be used while grouping.
  4. In Number of days, enter 7 (or use the spin button to make the change).
  5. If you click OK at this point, your data would be grouped by weeks starting with the first date in your data set.
  6. Click OK.
 
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.