Archive | Excel Tips: Mastering PivotTables and PivotCharts


Mastering PivotTables: Part 14 (Conditional Formatting in PivotTables)

Conditional formatting helps you visually explore and analyse data, detect critical issues/problems, and allows you to identify pattern and trends in the data. It is important to understand the differences when using conditional formatting on a PivotTable. There are three methods for scoping the conditional format of fields in the Values area: by selection, by […]

Continue Reading 0

Mastering PivotTables: Part 13 (Timelines)

Timelines are similar to Slicers, except that it is only limited to showing data related to Dates. Timelines allows easier visualization of data in a set amount of time, and allows easier analysis of data. Open this document (same document as Slicer) or open any Excel document that has data related with Dates. Navigate to the […]

Continue Reading 0

Mastering PivotTables: Part 12 (Slicers)

One prominent problem with PivotTable report filters is the need to constantly open the drop-down list to see which multiple items are activated. A slicer can easily show you which items are active and inactive, making it easier to understand the data. Slicers can also allow you to easily filter the data in a PivotTable, […]

Continue Reading 0

Mastering PivotTables/Charts: Part 10 (Using Tables as Data Sources)

By turning a range of cells into an Excel table, you can make managing and analyzing a group of related data easier. A table typically contains related data in a series of rows and columns in a worksheet that have been formatted as a table. By using the table features, you can then manage the […]

Continue Reading 0

Mastering PivotTables/Charts: Part 9 (Dynamic Data Ranges)

In Excel, you may have a named range that must be extended to include new information. The only way to do this is to create a dynamic defined name for the data range. When defining the data range for a PivotTable without dynamic ranges, if the range expand or contracts, then the definition of the […]

Continue Reading 0

Mastering PivotTables/Charts: Part 8 (PivotCharts)

PivotCharts can be created from the PivotTable data, allowing you to create charts to visually represent your data. Using any documents, or the document attached, follow these steps. Ensure the data is collapsed to yearly data (this can be done by grouping the dates in years). Right-click in the grand total figures, and sort the […]

Continue Reading 0

Mastering PivotTables/Charts: Part 7 (Grouping)

Grouping in a PivotTable can help combine data in a more meaningful way, especially with dates. Sometimes, companies will have data for individual days which makes the data visually unappealing, and with grouping, you can group the date in Weeks, Months, Year, and so on. First of all, please use this document to make the […]

Continue Reading 0

Mastering PivotTables/Charts: Part 6 (Expanding and Collapsing Detail)

Another Excel function for PivotTables is to expand and collapse detail for your PivotTables, thus only showing specific information to certain people. For example, expanding detail for people who are specialized in the business (HR, Finance, etc.), and collapsing detail for the general public or even for presentations to a group. From the previous sections, […]

Continue Reading 0

Mastering PivotTables/Charts: Part 5 (PivotTable Formatting – PivotTable Styles))

Continuing from the previous section, PivotTable styles will make your PivotTable more aesthetically pleasing. I will teach you about the two remaining sections in the Design Tab – PivotTable Style Options and PivotTables Styles. To start off, open the document used from the previous section. PivotTable Style Options In the Design Tab, this section shows […]

Continue Reading 0

Powered by WordPress. Designed by WooThemes

Free WordPress Themes