How To Count Items And Make Pie Charts In Microsoft Excel

Updated on November 20, 2014 savateuse moreContact Author 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. I'm going to use a simple stock list spreadsheet for this exercise. 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.

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 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. 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.

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.

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.