Learn to Use Excel - The Basics
Welcome back!
Friday is here yet again, I know you are excited because it is Finance Friday! Recently, we looked at a couple of tools that we can use to improve our bookkeeping processes. One of the tools I mentioned was Excel. Today I’m going to share a few basic formulas and functions that are useful in Excel. In a few weeks, we will take a look at more advanced formulas/functions.
Below you will find a screenshot from an Excel spreadsheet that I made for this blog. Excel is organized with columns labeled by letters starting with A and rows that are labeled with numbers starting with 1. Excel spreadsheets are huge. They don’t go on forever, but at times it seems like they may.
For this tutorial, I have kept it simple. I have organized our data by month in column A then I have volume, Price Per Unit and the total for each month. In line five I have the total volume and total dollars in column D. When you are telling somewhere to look in an excel file you will give them the cell location. If I said what is the total in D5? You would look to column D down to row 5 and see the total is $115.5
You can manually enter data into each cell. Just click on the cell and start typing just like you would do in any other program. If you want to create a formula, click in the cell and hit the equal sign button (=) then start your formula. You will see your formula in the bar at the top of Excel. Below is a screenshot of starting the formula in D2. You can type either in the cell itself or in the bar at the top of the screen.
Formulas are so important in Excel. Excel is a powerful tool. If you understand Excel, you can create so many programs and functions. Don’t get overwhelmed. Even just a basic knowledge of Excel will be helpful, and the basics are easy to understand.
For our example, we want to see the total dollar amount for each month. This means we will need to multiply column B by column C. This is super simple. After the equal sign, you will add the following formulas =B2*C2 and hit the enter or tab button to move to the next cell. Once you have done this, you will see the amount update in D2.
You can either manually type the formula into the cell or use the mouse. After you enter the = sign, you can click on each cell you want. You can do the same thing if you need to add, subtract, or divide. The only difference is that you will need to adjust the sign.
Addition = +
Subtraction = -
Division = /
Multiplication = *
You can drag the formula in D2 down to the cells below it. This will bring the same formula down, but updated it for the new row. In D3, the formula will show =B3*C3. To drag a formula put the cursor in the cell that contains the formula you want to drag. You will see the dark box around the cell you are currently in. At the bottom right of the cell, there is a small box. Click on the box with your mouse and drag it down. You can also copy the cell that contains the formula and paste the formula down to all the cells
In row 5 we have the totals for volume and total. This is an easy formula. When you have a lot of data, you can use this formula, and it will add together everything in the range. In the Excel world, range means the cells you include in the formula. For this example, column B is looking at the volume we sold. B5 will have a range of B2 through B4 or B2:B4. When you want to do a sum formula, you click in the cell you want the total to appear, here would be B5, then enter the formula, =SUM(B2:B4). This formula is saying “this cell equals the sum of the cells from B2 through B4”.
*Remember when working with Excel, each formula is essentially saying something. You are writing code with the formula telling the computer what to do. Down the road, if you run into trouble with more complex formulas take a step back and “read” your formula, sometimes that can help to find errors.
The Last thing we are going to talk about today is formatting. When you use Excel taking the time to format the spreadsheet will make the data easier to understand and read. For instance, if you look at our example we have been using are columns C and D dollar amounts or just numbers? You can’t tell as it is, but we can format that.
First, highlight the cells you want to format the same way.
Then either right click and select format cells. This box will pop up, and you can select how you want to format the cells. For this example, we want to select the currency category. You can then select how many decimal places you want to show, and the format of the currency. Once you have selected the options, you want then click ok.
Now when you look at those cells, it is clear to any user of the spreadsheet that those items are dollars.
If you want to make it look even better, you can add lines and colors. Maybe highlight the total in yellow, so it stands out. To do that highlight the cells in the total line and click the paint bucket
, select the color you would like to see.
I will highlight the top row the same way and highlight it blue. Then highlight the three rows of data and highlight it white. By highlighting it white, we will not see the gridlines anymore.
I also think it will look nice if we add some lines around it. I will use this button to add lines:
.
When I finish formatting my data now looks like this:
This looks appealing to the eye, it is easy to read and clear to understand. Just by using a few basic steps you can provide great data to use for reports, presentations, and your own analysis.
Today’s lesson was just the some of the basic functions to get you started if you have no experience with Excel. Keep in mind that there are many different ways to do the same task in Excel. I tried to show here the most basic and easy to understand path. Every few weeks I’ll be back with more complex functions that you can use. If you have any questions, feel free to reach out! Hope you found this helpful.
Come Back Next Week!
*We hope you enjoyed our blog. Please note that the intent of this blog is to provide general information and should not be construed as financial, financial tax, accounting, legal, consulting or any other type of advice regarding any specific facts and circumstances, nor should they be construed as advertisements for financial services.
