How to Make a Tally Graph in Excel
By C. Taylor
Tallying certain criteria in your Excel 2010 spreadsheet totals the number of times the criteria appears in that document. For example, if you have a list of customer orders and want to tally the payment method -- such as cash, check or credit card -- you can use Excel's COUNTIF function to counts the number of times a value appears within selected data. This function produces the tally count, but to better illustrate the tally and compare the resulting figures, use Excel's charting tools to graphically display the results.
Open your data in Microsoft Excel 2010 and locate the column containing the tallying criteria. For example, you might have columns labeled "Order Number," "Customer Name," "Payment Type" and "Total" in columns A through D, respectively. To count the payment method in this example, you need to search through payment types in column C.
Type the individual tallying criteria in a new column, making sure to use the exact text used in your data. As an example, you might list "Cash," "Check" and "Credit Card" in cells E1 through E3.
Use the COUNTIF function in a new column to tally the results. Enter the data range and tallying criteria, separated by a comma, in parentheses after the function. In the example, enter "=CountIf(C:C,E1)" (without quotes) in cell F1. Doing so forces Excel to look through all of column C for the tallying criteria you entered in cell E1.
Click the cell you just entered and press "Ctrl-C." Hold the "Shift" key, click the cell that corresponds to the last tallying criteria, which is F3 in the example, and press "Ctrl-V" to copy the formula.
Click and drag across the tallying criteria and tallying results. In the example, this selects cells E1 through F3.
Click the "Insert" tab, choose "Column" from the Charts group, and then click the first bar chart listed in any of the sections. For example, select the first bar chart from the 2-D Column section to create a simple two-dimensional tally chart.
Tips
You can also use the COUNTIF function to count values over a certain amount. To change the example to count totals over $1,000, use the following formula:
\=CountIf(D:D,">1000")
Other useful operators are "<" to count values less than a certain amount, ">=" to search for values greater than or equal to a certain amount, and "<=" to find values less than or equal to a certain amount. To search for tallying criteria contained in another cell, add "&[reference]." For example, to search for values less than or equal to the value in cell E1, use the following formula:
\=CountIf(D:D,"<="&E1)
References
Writer Bio
C. Taylor embarked on a professional writing career in 2009 and frequently writes about technology, science, business, finance, martial arts and the great outdoors. He writes for both online and offline publications, including the Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym and more. He received a Master of Science degree in wildlife biology from Clemson University and a Bachelor of Arts in biological sciences at College of Charleston. He also holds minors in statistics, physics and visual arts.