For those who regularly use Excel, the number of built-in formulas and functions for summarizing and manipulating data is overwhelming. Excel is used by literally everyone, from finance students to Wall Street hedge fund managers. This is a very powerful, yet very simple way.
For those new to Excel, one of the first groups of functions you should learn is the Pivot Functions. These include SUM, AVERAGE, MAX, MIN, MODE, MEDIAN, COUNT, STDEV, LARGE, SMALL, and AGGREGATE. These functions are best used with numeric data.
In this article, I will show you how to create a formula and insert a function into an Excel spreadsheet. Every function in Excel takes arguments, which are the values ??that the functions need to calculate the output.
Understand formulas and functions
For example, if you need to add 2 and 2 together, the function will be SUM, and the arguments will be the numbers 2 and 2 . We usually write this as 2 + 2, but in Excel, you should write it as = SUM (2 + 2). Here you can see the results of this simple addition of two literal numbers.
While there is nothing wrong with this formula, it is unnecessary. You can just type = 2 + 2 into Excel and that will work too. In Excel, when you use a function like SUM, it makes sense to use arguments. When using the SUM function, Excel expects at least two arguments to be cell references in the spreadsheet.
How do we refer to a cell inside an Excel formula? Well, it’s pretty simple. Each row has a number and each column has a letter. A1 is the first cell in the table at the top left. B1 will be the cell to the right of A1. A2 is the cell directly below A1. Simple enough, right?
Before writing our new formula, let’s add data to columns A and B to work with. Go ahead and enter random numbers A1 to A10 and B1 to B10 for our dataset. Now go to D1 and enter = SUM (A1, B1). You should see that the result is simply A1 + B1.
There are several things to note when entering a formula in Excel. First, you will notice that when you enter the first open parenthesis (after the function name, Excel will automatically tell you what arguments this function takes. Our example displays number1, number2, etc., the arguments are separated by commas. The specific function can take an infinite number of values, since this is how the SUM function works.
Second, you can manually enter a cell reference (A1), or click cell A1 after you have entered an open parenthesis. Excel will also highlight the cell in the same color as the cell reference so you can see the corresponding values ??exactly. So, we’ve summed one row together, but how can we sum all the other rows without retyping the formula or copying and pasting? Fortunately, Excel makes it easy.
Move your mouse cursor to the bottom right corner of cell D1 and you will notice that it changes from a white cross to a black plus sign.
Now click and hold the mouse button. Drag the cursor down to the last line of data, then release it at the end.
Excel is smart enough to know that the formula needs to change and reflect the values ??in other rows, rather than just showing you the same A1 + B1 all the way down. Instead, you will see A2 + B2, A3 + B3, etc.
There is also another way of using SUM which explains another concept of arguments in Excel. Let’s say we wanted to sum all values ??from A1 to A12, then how do we do that? We could enter something like = SUM (A1, A2, A3, etc.), but it will take a very long time. Better to use an Excel range.
To sum A1 to A12, all we have to do is enter = SUM (A1: A12) with a colon separating the two cell references instead of a comma. You can even enter something like = SUM (A1: B12) and it sums all values ??from A1 to A12 and B1 to B12.
It was a very basic overview of how to use functions and formulas in Excel, but it is enough to get you started using all the data summarization functions.
Summary functions
Using the same dataset, we’re going to use the rest of the summary functions to see what numbers we can calculate. Let’s start with the COUNT and COUNTA functions.
Here I introduced the COUNT function in D2 and the COUNTA function in E2, using the range A2: A12 as the dataset for both functions. I also changed the value in A9 to the hello text string to show the difference. COUNT only counts cells with numbers, while COUNTA counts cells containing text and numbers. Both functions do not count empty cells. If you want to count blank cells, use the COUNT EMPTY function.
Next are the AVERAGE, AVERAGE and MODE functions. Average is self-explanatory, median is the average of a set of numbers, and mode is the most common number or numbers in a set of numbers. In newer versions of Excel, you have MODE.SNGL and MODE.MULT because there can be more than one number, which is the most common number in a set of numbers. I used B2: B12 for the range in the example below.
Moving on, we can calculate MIN, MAX and STDEV for the set of numbers B2: B12. The STDEV function will calculate how far the values ??deviate from the mean. In newer versions of Excel, you have STDEV.P and STDEV.S, which are calculated based on the entire population or on a sample basis, respectively.
Finally, two more useful features are LARGE and SMALL. They take two arguments: the range of cells and the k-th largest value you want to return. So if you want the second largest value in the set, you should use 2 for the second argument, 3 for the third largest number, and so on. SMALL works the same way, but gives you the kth smallest number.
Finally, there is the AGGREGATE function, which allows you to use any of the other functions mentioned above, but also allows you to do things like ignore hidden lines, ignore error values, and so on. You probably won’t need to use it as often. , but you can learn more about it here if you need it.
So this is a basic overview of some of the most common pivot functions in Excel. If you have any questions, do not hesitate to leave comments. Enjoy!
–