Excel is the best piece of software ever made.
Excel is a powerful, daunting program, and a lot of people barely know where to start. We’re here to help.
If you’ve never used Excel before and want to, or if you’re just starting out and want to make sure you’re going on the right track, we’ll show you some of the most fundamental concepts and ideas to get you well on your way to becoming an Excel whiz kid.
2. The first thing we could do is double click the bottom right hand corner. This will fill in the rest of the rows in the G column, provided there is data on that row in the F column.
4. Now let's look at SUMPRODUCT. Let's say the business we described in the first table sold its products for different prices on different days, indicated in the second table.
5. Let's say we want to find out the average number of items we sell on a given day. You'd use the AVERAGE formula.
6. Now we're going to talk about using dollar signs in formulas. Let's try to make a simple times table. In the first cell, we type in the formula for 1 times 1.
When we copied the cell over, it copied the formula over exactly as written. What we need to do is fix the original formula so that doesn't happen.
So when we copy that over it looks like this. Now let's copy this row down to fill in the rest of the table.
So even though the first term of each equation is locked into Column A, the second term isn't locked into Row 1. As a result, when we copied the formula down, it adjusted the position of the second term.
Let's fix the formula and write B$1 instead of B1. This will lock in the second term to be the relevant value on row 1.
See how in the formulas of each cell, each of the column and row values that don't have a dollar sign in front of them shift with each cell, while the rows or columns with a dollar sign remain either A or 1.
7. Let's say you wait tables for a restaurant in a state that requires you make an $8 minimum wage every hour. If you make more than $8 in tips, the restaurant doesn't pay you. If you make less, the restaurant pays you the money you need to make it to $8.
In this example, we're going to use the IF function. We're going to program it so that if the value in column B is less than $8, we replace it with $8.
The first part of the IF function is the logical test. This will be evaluated whether it is true or false.
The next part tells it what to put in the cell if the logical test is true. In this case, if the value in B2 is greater than 8, we just want to put B2 in that cell.
The last part is what to do if the logical test is false. If B2 is not greater than 8, then we want to make it $8.
Once we fill in the whole column, you'll see how any value less than 8 has been replaced with 8, while all values greater than 8 are the same. This is a simple example of how to use the IF function.
8. Let's say we run a simulation of 400 rolls of 2 dice. We then use an IF statement (in column C) to find out if they're doubles. If we want to know how many doubles there were, we can use the COUNTIF function.
The COUNTIF function will tell you how many elements of a selected set match a given criterion. First, we select the given set of responses.
Business Insider Emails & Alerts
Site highlights each day to your inbox.