Excel Mastery: Crush Your Spreadsheets Like a Pro! Part 4.2 – Understanding Functions
Excel Mastery: Crush Your Spreadsheets Like a Pro!
Data Manipulation in Excel
Part 4.2
Understanding Excel’s Functions and how to Build Formulas
Using formulas and functions
Excel is a powerful tool that can help you manipulate data fast. With the right formulas and functions, you can transform raw data into understandable outputs.
The first part of these magical keys is called formulas. In most cases, the terms ‘formulas’ and ‘functions’ can be interchangeable. Technically, they are different. You can call functions with a formula, and because of this, people confuse them as being the same. For now, let’s talk formulas.
What are Formulas
Formulas are like the secret sauce that makes Excel so delicious. They’re what allow you to perform complex calculations and analysis with just a few keystrokes. Need to calculate the average of a set of numbers? Just use the AVERAGE function in a formula (see what I mean by them being somewhat the same?). Want to know the maximum value in a range? Use the MAX function. It’s like having a personal mathematician at your fingertips, minus the math degree and the pesky salary.
Excel has a plethora of functions you can call for pretty much everything. If you can think of it, Excel either has a function for it, or you can achieve it through VBA (something for a later topic, which we will cover in GREAT detail). Pretty much, Excel can do things in ways you never thought possible.
Need to extract a substring from a cell (a String is basically Text – again, we will cover this later)? Use the LEFT, RIGHT, or MID functions. Want to concatenate two or more cells? Use the CONCATENATE function. And my personal favorite, the IF function, which allows you to create conditional statements that would make even Sherlock Holmes proud.
What most people don’t know, every software, app or program you have ever used, is nothing more than a crap-ton of If statements. If statements are basically Yes or No response. Using Yes or No questions, you can ask a computer to do… well, pretty much anything. You just have to think of a way to have a certain question answered in either a Yes or No fashion. If you can do that, you can make a computer do anything.
How to use these Functions and Formulas
Let me start by showing you how functions work inside of cells.
When you go to a cell you can add a function by typing in an equals sign ( = ). This lets Excel know that you are going to be telling it something to do. Every function will start with this. Next you can just start typing in a function that you need and a list of options will appear underneath where you at typing.
Now this part is one that I find extremely annoying with Excel. If you want to select one of the items from the dropdown-auto-populate list, you need to mouse over to it and double click. You can not use the up and down arrows to select it, you have to mouse over and double click.
Example:
In this example I am going to show you the simplest and basics of functions in Excel. Excel is a calculator. Yes, I bet that most of you already know this, but this example is a great introduction to functions in Excel.
Put the number 5 in cell A1. Next, put 7 in B1. In C1, type the following and then press enter:
=A1+B2
OR
You can also achieve the same outcome by selecting the cells instead of typing them. For example, you can type in cell C3 “=”, then click on A1 and Excel will automatically populate the cell name into the formula.
You can see that not only was the cells A1 and B1 highlighted, but now C1 is 12, or A1 (5) + B1 (7). Pretty neat, huh?
Math Equation Formulas
Well, that is just the bare-bones of what Excel can do. Honestly, that is more like the marrow inside the bone.
If you want to use Excel to calculate things, you can use the following set of mathematical variables. While I have the top five listed below, I want to also point out that I have a front-and-back printable sheet that has the top 41 functions to call in Excel. You can find your free printable PDF here. You can use any of the functions listed there, or below.
- Addition: + – used to add two or more numbers, cell references, or ranges together. For example, “=A1+B1” would add the values in cells A1 and B1.
- Subtraction: – – used to subtract one number, cell reference, or range from another. For example, “=A1-B1” would subtract the value in cell B1 from the value in cell A1.
- Multiplication: * – used to multiply two or more numbers, cell references, or ranges together. For example, “=A1*B1” would multiply the values in cells A1 and B1.
- Division: / – used to divide one number, cell reference, or range by another. For example, “=A1/B1” would divide the value in cell A1 by the value in cell B1.
- Exponentiation: ^ – used to raise a number, cell reference, or range to a power. For example, “=A1^B1” would raise the value in cell A1 to the power of the value in cell B1.
How to Become Better at Excel Formulas
If you really want to become great at using functions and creating formulas, the best way to learn how, is to just goof off with it. Seriously. Start goofing around with the tools above, and then move on to some more advance functions.
Not only do we have the top 41 Math Functions above, we also have the more double-sided cheat sheets as well. Just look in the box below.
BUT, if you really want to get the most out of Excel, you might want to check out CelTools. CelTools is an Excel Add-in that contains over 70+ functions and features. The best part is that CelTools was specifically made for any office worker or manager to truly unlock their full Excel potential.
But wait, there’s more!
If you love this free cheat sheet, we are offering our complete PDF Cheat Sheet Collection here!
This collection contains all of our concise Excel cheat sheets as well as two more PDFs exclusive to the bundle. That is a total of 15 pages that outlines every think you need to know to supercharge your Excel experience!
This Bundle includes:
* The Top 25 Excel Keyboard Shortcut Cheat Sheet
* The Ultimate Excel Guide: Top 80 Functions
* The Top 25 Excel Conditional Formatting Cheat Sheet
* The Ultimate Excel Guide: Top 41 Math Functions
* The Top 20 Advanced PIVOTTABLE Techniques **EXCLUSIVE**
* The Ultimate Excel Guide: Top 50 Macro Calls
* The Complete Excel Chart Cheat Sheet **EXCLUSIVE**
Purchase your copy here! For less than a cup of coffee you can have all of Excel’s secrets at your finger tips!
Remember, it’s not just a cheat sheet; it’s your journey to Excel efficiency.
Conclusion
Excel is a treasure trove of formulas and functions that can help you manipulate data fast. With a little bit of creativity, you can turn raw data into information that is easily understood.
