Excel Like a Champ pt. 2: How to Make and Use Formulas
Hello friends! Oh how I have missed you over these past few days. The only thing that kept this distance apart okay was knowing that you guys were all doing your excel homework (because I trust that all of you did it and are ready to begin step two of becoming an absolute excel PRO)!
Now that you all have played around freely on excel and are comfortable and hopefully not overwhelmed, let’s pick up where we left off. As a reminder, we have inputted some basic data to get started with so we can practice using shortcuts and some formulas. This is what your excel sheet should look like:
We are going to start getting into using some basic functions for to determine the total. What you should do is click G2 (the total of Artist 1).
In G2, type in “=SUM(B2:F2)” and hit enter. Magically, 62 will come up, the sum of all of those cells. The = sign lets excel know you are doing a formula, “sum” lets excel know you want to add all of those values, and the “B2:F2” lets excel know that you want the sum of all of the values between and including cells B2 and F2. It’s so easy, quick, and efficient.
It gets better though, because you can do this with many advanced functions, not just the total. Let’s add some new labels. In H1, next to total, let’s add “average.” In I1, let’s add median, and in J1, let’s add “count.”
In H2, let’s find the average. You can use the exact same formula as G2, just replace the word “sum” with “average,” to which you should get a value of 12.4. You can continue this process for median and count, to which should get you a median of 10 and a count of 5. What you are essentially doing for Artist 1 is creating your own basic formulas to get information very quickly. Within seconds, I can tell you important information and I didn’t even have to do any math! Pro tip: If you aren’t sure what functions there are, click the cell you want to preform the equation in, put in the = button, and then click the “fx” written up top right underneath the italic symbol at the top of the excel sheet. This should let you see lists of all different functions you can do (some more advanced than others). This makes it extremely quick and easy to make equations, and lets you see all of the options for what functions you can preform.
Writers note: I do want to emphasize that there are multiple ways to make a formula, the way I taught you of typing it in with the colon IS JUST ONE WAY. I’m not going to go over every single way to do it because we would be there until next year, but I just wanted to show you guys how I personally like to do it.
Now, you may be thinking, “Great Zoe. We know how to make formulas. That doesn’t change the fact we are going to have to do that for many different columns, even 100s or 1000s depending on how much data we have.” And that would be a great point. BUT, I have an exciting tip to show you how to avoid that.
Let’s say we want to get the total for the rest of the artists’ song sales (keeping in mind that only doing it three times isn’t a lot, but you may be working with over 100 columns instead of just three!) All you need to do is is click on cell G2 (the total for Artist 1 where you made your formula, and then double click on the green square in the bottom right hand corner of the cell and……..
TA-DA! All of the totals magically appear. Imagine you needed to find the totals for over 250 artists, you could do that by typing in one formula and would get all of the totals within 1 second. Excel is a beautiful thing, isn’t it? You can do this for all of your formulas. You can do this same thing for the average, median, and count formulas (as well as any other function you use)! This works because it automatically let’s excel know you want to use that formula for each artist, and uses the same formula while changing the actual cells so its correct for each artist. If you click on G3, which now says 54 as the total song sales for Artist 2, you can look at the top which has the formula written “=SUM(B3:F3).” Excel is smart enough to change is to the 3 column data so that the same formula applies, just with the correct numbers.
If you are still will me, I am super proud of you for sticking it out so far. We have learned what took me about 2 weeks on the job to really figure out in about 1 hour, and I could not be more proud of you. This is a lot of information, so I am going to give you guys time to soak it all in, and perhaps practice on your own.
Until next time, future excel champs,
Your favorite excel teacher, Zoe
