• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Archives for PivotTables

4 Ways to Group Time in Excel – PivotTables and Formulas

If your spreadsheet contains a list of times, it is useful to group time in Excel into intervals such as 30 minutes, hourly or every 6 hours.

Fortunately, Excel provides a variety of methods to group time depending on what you need. In this blog post, we look at 4 ways to group time in Excel using PivotTables and formulas.

[Read more…] about 4 Ways to Group Time in Excel – PivotTables and Formulas

5 Advanced PivotTable Techniques

PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques.

  1. Grouping fields by month and year
  2. Calculating data as a percentage of the total
  3. Using Slicers
  4. Applying Conditional Formatting to PivotTable data
  5. Creating calculated fields

If you are new to PivotTables, check out our introduction to PivotTables tutorial.

[Read more…] about 5 Advanced PivotTable Techniques

Count Unique Values in a PivotTable

When using a PivotTable to summarise a large list of data, you may need to count unique values in a PivotTable for a particular field.

For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.

Data set to count unique values in a PivotTable

If you added the Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.

Normal count calculation in a PivotTable

However, this is counting all of the transactions in each country. We need to count the unique customers making those transactions in each country.

From Excel 2013, there is a Distinct Count option in PivotTables making counting unique values a piece of cake. In earlier versions there is a workaround.

[Read more…] about Count Unique Values in a PivotTable

Rank Fields in a PivotTable in Excel

In Excel, one of the PivotTable calculation options is to rank fields in a PivotTable.

Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.

Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.

PivotTable with sales and conversion rate

We would like to add a rank field to the PivotTable for both fields.

[Read more…] about Rank Fields in a PivotTable in Excel

Using Timeline Slicers in Excel 2013

Timeline Slicers are a new addition to Excel 2013. They are a new filter tool that is available exclusively to the date fields in your PivotTables. They are visually appealing and easy to use.

To use a Timeline with your PivotTable;

[Read more…] about Using Timeline Slicers in Excel 2013

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to Next Page »

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·