ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Count Items and Make Pie Charts in Microsoft Excel

Updated on November 20, 2014

Counting Items on an Excel Spreadsheet?

Counting data entries is a topic that often puzzles users of Microsoft Excel and other spreadsheets, but it's actually not so difficult to do.

It's something we don't tend to think about when first learning to use Excel. Our first priorities are usually setting up a spreadsheet, formatting it or adding up numbers and currency in the columns. But, eventually there comes a time when we don't just want to know the total or average, we actually want to know how many times a particular value (number or text) occurs.

Here's an example of the kind of situation: if we were running a supermarket, we might want to know how many different types of fruit we usually stock. The information is probably there, on our stock list. We could use the spreadsheet filters, and then count, but there are ways to increase our efficiency. Here I will show you how to use the status bar for counts, averages and other numerical data values. I will also show you how to use the COUNTIF function to count text data items.

All the text, photos and diagrams on this page are my own. They are copyright to me, so please don't use them without my written permission and a link to this page. Thanks!

My Stock List Worksheet

I'm going to use a simple stock list spreadsheet for this exercise. My data includes twenty different food items, in five different food types:

Dairy

Fruit

Grains/Cereals

Proteins

Vegetables

The Status Bar in Excel

The first thing to know is how to use the Status bar - a horizontal bar at the bottom of the Excel window that gives information about the current mode and any special keys you use, such as CAPS LOCK. It allows you to zoom in and out or change between the worksheets layouts, for example from NORMAL view to PAGE BREAK preview. In addition, it has an AutoCalculate indicator that can how many cells are in a selection, the average and/or sum of all the numerical entries in a selection.

To customize the status bar, right-click on it to view the menu. As you can see there are many choices to select or de-select depending on the task in hand.

Using the Status Bar

Selecting Column D gave the following results:

From left to right, you can see that:

the average value in the cells is 9.9

the total number of items in the column is 21 (including the words "Units in Stock")

the total number of numerical items in the column is 20

the minimum units in stock is 2

the maximum units in stock is 20

the total number of items in stock is 198

That's a lot of information already!

I can use the same technique for column C to discover, for example, the average cost of an item on my stock list. For the text data in columns A and B, however, I can only get the item count of 21.

Excel 2013 Bible
Excel 2013 Bible

The excellent Excel Bible updated for Microsoft Excel 2013 users

 

The Excel Bible

For anyone who really want to understand how to use spreadsheets in Microsoft Excel, you will find the Excel Bible very useful. Author, John Walkenbach is considered by many to be one of the top writers on using Excel.

If you are using Excel 2010, and don't intend to upgrade to Excel 2013, you will need the 2010 version of Mr Spreadsheets bible.

Editing Group

I can also use the Editing Group, on the Home Tab, to add up, count and find the averages of selections of number data. If I click on cell C22, to make it the active cell, then click on the Autosum button in the Editing Group, the program will enter a formula into the cell. If I agree the formula, by pressing the Enter Key, the formula will be processed - in this case it gives me the answer $56.89.

Note: this is not a particularly useful thing to do, as all I have achieved is to find the cost of "one of everything" on my stock list. It would be more useful, for example, to select Max from the Autosum drop-down menu to give me the maximum price of an item. I just give this as a reminder that you should have decided what you want to achieve before you start applying formulas hither and thither...

But, what about Counting the Text Data?

Yes, it is possible!

If I select "More Functions" from the Autosum drop-down menu, a new window will pop-up. This offers me a search box or categories to choose from, to help me find the formula I need. When you select a function from the list, an explanation appears in bold type underneath. In this case, for the function "Count". You can see that this function would only be useful for numerical data.

However, if I use the function "COUNTIF", I am not restricted to numerical data.

Agreeing to use this function (by clicking OK), brings up the "Function Arguments" window. Here I have to enter the range of cells I'm interested in. In this case the data containing cells in Column A, which is represented by A2:A21. In English, that's cells from A2 to A21 inclusive. I also have to enter the criteria I'm interested in. In this case "Dairy".

So, I have asked Excel to tell me how many times the word "Dairy" occurs in those cells. The function arguments window shows the answer of 4.

If I click OK, the active cell now shows the same result.

Making a Pie Chart in Excel

to show the Proportions of Different Food Types we Stock

Here is a little example of this function in use. First I will insert a new column to the left of food types, and enter the following data:

A23 - Dairy

A24 - Fruit

A25 - Grains/Cereals

A26 - Proteins

A27 - Vegetables

Then, I add the "CountIf" function to the following cells:

B23

B24

B25

B26

B27

e.g. in B25, I enter

=COUNTIF(B2:B21,"Grains/Cereals")

Inserting a Pie Chart

Select the cells in the rectangle A23 to B27.

Click on the "Insert Tab", and select "Pie" from the "Charts group". You can choose from a 2-D or 3-D piechart. I have chosen 3-D.

I have also selected a dramatic black background for my piechart from the "ChartStyles" on the "Design Tab".

And finally, I have labelled my piechart to show the percentages of the different types of food held in stock.

Teach Yourself Excel

Microsoft Excel 2010 Introduction Quick Reference Guide (Cheat Sheet of Instructions, Tips & Shortcuts - Laminated Card)
Microsoft Excel 2010 Introduction Quick Reference Guide (Cheat Sheet of Instructions, Tips & Shortcuts - Laminated Card)

This is a really useful quick reference card for those who have begun to use spreadsheets, but are not yet fully confident.

 

© 2013 savateuse

Do You Use Excel? - Do you have any useful tips and tricks to share?

    0 of 8192 characters used
    Post Comment

    • savateuse profile image
      Author

      savateuse 4 years ago

      @anonymous: Thanks for the feedback, it's good to know that you found this page useful!

    • profile image

      anonymous 4 years ago

      Thanks for this useful information. I'm using OpenOffice, which has a countif function, too, which I wasn't aware of up until now. Makes my life easier. Thanks.

    • greenspirit profile image

      poppy mercer 4 years ago from London

      Thank you for such a helpful guide to Excel. It's a programme I struggle with, so this will be really useful!

    • savateuse profile image
      Author

      savateuse 4 years ago

      @dudokdudok: Thank you! I wrote it hoping that it would help people learning Excel.

    • profile image

      dudokdudok 4 years ago

      This is really helpful. I sometimes struggle with Excel. This is a nice summary of useful things in Excel. Great lens.

    • savateuse profile image
      Author

      savateuse 4 years ago

      @hovirag: I puzzled over this one for a while, and eventually decided I had to learn how to use more of the Excel functions, like COUNTIF. ANd, it's actually very useful stuff to know.

    • VineetBhandari profile image

      VineetBhandari 4 years ago

      Thanks for this information.

    • hovirag profile image

      hovirag 4 years ago

      This is what I still have to learn! good information

    • savateuse profile image
      Author

      savateuse 4 years ago

      @techmom: I know a few Excel users who don't know how to count text items. That's why I wrote this page :)

    • techmom profile image

      techmom 4 years ago

      I did not know you could count text! Thanks!

    • LisaAuch1 profile image

      Lisa Auch 4 years ago from Scotland

      My husband is the excel wizard in our house, but your how to's make it easier for me to understand :)

    Click to Rate This Article