5 Excel Formulas
you need to know
Sydney's seminar
While many of us use Microsoft Excel for work, most have never been formally trained or taught how to get the most out of this effective software. When our clients at CLARK request Excel training, we commonly hear that people want to learn how to use formulas to allow for a more sophisticated and helpful spreadsheet.
But many don’t realize that using formulas can also help make your job a whole lot easier.
In this blog, I’m going over the 5 formulas in Excel that you need to know.
SUM
SUM is one of the most frequently used Excel formulas, and there are multiple ways to use this function. The SUM function lets you get a total sum from a selected data group. The function is as follows: =SUM(number1, number2, …). In the formula, you enter the cells in place of the numbers that you want to be added (Fig. 1). If you want to add a group of numbers in a row, you can indicate this by using the formula =SUM(Cell1:LastCell), which in our example would be =SUM(B2:B6).
If you don’t feel like typing in the formulas, don’t worry, there are multiple shortcuts! The easiest is the AutoSum feature. Simply highlight the cells you want to be summed and then click AutoSum. The button can be found in the Home tab in the Editing section. A second shortcut is to type in =SUM( and highlight the cells you want to sum, finishing by hitting enter.
This auto-fills the rest of the formula for you.
AVERAGE
Many of us working with data will want to find the average of a group of numbers, which is made easy with the Excel AVERAGE function. This is very similar to the SUM function, where you create the formula with the cells you want to be averaged either highlighted or listed (Fig.3).
Luckily, you can also use the AutoSum function by clicking the dropdown and selecting Average.
COUNT
The Count function can be extremely helpful in creating a tally of the line items of data you have. With this function, the output will be a tally count of all the cells with numbers. This may be helpful if you need to know how many occurrences of data you have. The formula is built the same as SUM and AVERAGE, as =COUNT(value1,value2,…). This function can also be found in the AutoSum dropdown.
MAX/MIN
The MAX and MIN functions are particularly helpful when you have A LOT of data to manage that you don’t want to sort through. Both functions will provide you with the largest or smallest value in a set of values, ignoring the text and logical values.
Although this formula is beneficial when using large amounts of data, there is a shortcut you can use for smaller sets of data. Simply highlight the row you want the max and min of, and sort from largest to smallest (or vice versa).
SUMIF
While more complicated than the other functions in this group, the SUMIF function is a need-to-know one. This function allows you to sum a set of numbers only if they meet a specified criterion. For example, maybe you want to sum all of the purchases in your budget, but only those over $20. In this case, you would use the formula =SUMIF(B2:B36,”>20”).
Since this formula is a little more complicated, you can use the function wizard to help you. Highlight the cell you want the formula to be in, and then click the insert function button ( ), which is located next to the formula bar. Here, search for SUMIF, highlight the result and click OK.
The Function Arguments wizard gives a fill-in-the-blank option to help build the formula with you. This is made extra handy by providing you definitions of the formula and a hyperlink for more information about the function.
At Clark Computer Services, we love Excel and use it to make our work more efficient, which is why we recommend it to our clients. If you have any questions or want more training on Microsoft Excel, call us at 301-456-6931 or email us at [email protected].
Being raised by Clark’s owner, Darren, I have always been immersed in the world of technology. However, I have always followed it from a distance. I went to college to get my degree in Business Finance and Applied Economics, as I have always been a fan of research and statistics. I was even lucky enough to get my senior thesis in economics published. My next string of luck was getting a job straight out of college as a Researcher in Richmond, VA. I was able to pursue research and publish dozens of news articles in my field. Now, I am so excited to delve back into the world of technology that I was raised in, and look forward to honing my research in the technological field.
