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 imageAUTHOR

      savateuse 

      5 years ago

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

    • profile image

      anonymous 

      5 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 

      5 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 imageAUTHOR

      savateuse 

      5 years ago

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

    • profile image

      dudokdudok 

      5 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 imageAUTHOR

      savateuse 

      5 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 

      5 years ago

      Thanks for this information.

    • hovirag profile image

      hovirag 

      5 years ago

      This is what I still have to learn! good information

    • savateuse profile imageAUTHOR

      savateuse 

      5 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 

      5 years ago

      I did not know you could count text! Thanks!

    • LisaAuch1 profile image

      Lisa Auch 

      5 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 :)

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)