Search code examples
sqlexcelgroup-bycountcategorization

Group by and display count in Excel

I am trying to learn some Excel's features and I want to know if it is possible to make an SQL-like statement in Excel.

For example, I distincted a dataset and the output is:

date     name    class
20210101  ted     a
20210101  nick    b
20210110  george  c

I want to make a count(*) and a group by like:

select count(*), name, class
from table
group by name, class
Solution

  • Initial data:
    enter image description here

    Create a pivot table.

    When you drag columns like this...
    enter image description here

    ...your pivot table will look like this:
    enter image description here

    Right click on "Sum of date" -> "Value Field Settings..."
    Choose "Count" -> "OK"

    enter image description here

    If you like, you can separate those fields which are stacked in "Rows".
    Right click the value of outer level category in "Rows" (e.g. "ted") -> "Field Settings..."

    1. Tab "Subtotals & Filters", under "Subtotals" select "None"
    2. Tab "Layout & Print", under "Layout" select "Show item labels in tabular form"
    3. OK

    enter image description here