Practice makes perfect. We have to experiment and try new things to know how Excel works. Nobody has taught themselves how to make a spreadsheet in a day; and even those who do know how to work it don’t do it right the first time.
My mother often talks with me about how she wishes she knew more about the Excel program and I have always wished I could find that simple tutorial that helps her understand; so since I have yet to find it, I’ll create it. J I figure if she needs it there are others out there who either need it or just simply want it. Yes, I said a simple tutorial. So if you have used Excel before; this specific post may not be for you.
Microsoft Excel, Microsoft Word, Microsoft Powerpoint, and Microsoft Outlook are all Microsoft products that were created by the same company and can be used together to complement one another. Microsoft Word creates a letter. Microsoft Excel creates items like spreadsheets (i.e. budgets, tracking log, and schedules). Microsoft Powerpoint makes a presentation that has slides that you might use when trying to explain a process (I could use Powerpoint and 1 slide could represent 1 step for this explanation).Microsoft Excel has rows (number system going down the left side (i.e. 1, 2, 3, 4)) and columns (letter headings along the top (i.e. A, B, C, D)). The rows & columns make up a cell (i.e. A1, A2, B1, B2) in which you fill with information (either manually plugging in the necessary information or using built-in formulas (ADVANCED)).
To better understand how to use Microsoft Excel, let’s look at making a weekly budget.
Step 1: Think of your weekly expenses and put them in a list starting in A2 moving down towards A3, A4, A5, etc.
- Coffee (we all know this is a must!)
- Personal Care
Step 2: Uh oh! You will notice that the words “Personal Care,” and “Entertainment” break the grey line between column’s A and B. In order to fix this:
- Place your cursor (the arrow that your mouse move’s) between column headings A and B, click, and move your mouse to the right. This will make Column A wider.
- From here, you can continue to either make column A as small or large as you see fit.
Step 3: Now that we have our expenses, we need to record the necessary details about how much we expect them to cost and what they actually cost at the end of the week.
- Cell B1: put “Projected cost”
- Cell C1, “Actual cost”
- You will notice after typing “Actual cost” that it has covered up part of your information from cell B1; do not panic, your information is still there. Go ahead and click your cursor in cell B1. Now, similarly to step 2, move your cursor between column’s B and C, click, and move to the right. Reminder: From here, you can make column B as small or large as you see fit.
- NOTE: When you click in ANY cell, you will notice in the top blank white bar, which looks similar to a search bar, it shows you what is typed in the cell that you have selected.
- Cell D1, “Cost difference”
- At this point, you will probably want to follow the same instructions to make column C larger that you have previously done with columns A and B.
- You might also want to increase the width of column D so the words “Cost difference,” does not cross over the gray line.
YIPPEE!! 🙂 Can you begin to see what looks like a spreadsheet? 🙂 You’re almost there! The next steps are some of the “beginning to fun.”
Step 4: Now, fill in the number’s in the corresponding cells for projections.
- If we project the following expenses:
- Gas $50 (Type 50 into cell B2)
- Groceries $75 (Type 75 into cell B3)
- TIP: Notice, I am going in sequence down Column B? Once I have typed “75” into cell B3, I can click “ENTER” to get to cell B4.
- Coffee $10 (Type 10 into cell B4)
- Personal Care $20 (Type 20 into cell B5)
- Entertainment $40 (Type 40 into cell B6)
Step 5: I am talking about money; therefore I would like to show my costs (Projected, Actual and the difference) in currency form ($xx.xx).
- NOTE: Even though I have not filled in numbers for the actual and difference, we can still select those cells to be in currency form. Think of it like reversing steps, or being ahead of the game.
- Click cell B2 and hold down and go to cell D6 and let go of your mouse. Notice black lines have surrounded the cells from B2 to D6; the black lines get larger with the cells that you have covered.
- At the top in the Menu bar, under the Number section, there is a drop down box that currently says “General” (NOTE: this could say something else, but this is the usual for most computers)
- Click the arrow and click on “Currency.”
- You will see the numbers in column B now have changed from general numbers (i.e. 50, 75) to currency numbers (i.e. $50.00, $75.00)
- If at the end of the week we have calculated our actual expenses, we can now add them into the corresponding cells (under the actual column):
- Gas $55 (Type 55 into cell C2)
- Groceries $75 (Type 75 into cell C3)
- TIP: Notice, I am going in sequence down Column C? Once I have typed “75” into cell C3, I can click “ENTER” to get to cell C4.
- Coffee $20 (Type 20 into cell C4)
- Personal Care $10 (Type 10 into cell C5)
- Entertainment $40 (Type 40 into cell C6)
- NOTE: You will see that when you input the numbers for our actual costs, because we already did step 5 for column C, the numbers already show up as currency!
- I like to group things together as much as possible because it saves time.
- When you analyze the numbers for our budget (what we projected we would spend compared to what we actually spent, the numbers don’t match!). And typically, they won’t!
- Click in cell D2.
- Type “=”,
- then click on cell B2,
- type “-“,
- then click on cell C2
- and click enter.
- Cell D2 should say “-$5.00”. (If you do the math in your head $50-$55 it equals -$5; therefore you know you got the right answer.)At this point, I have two options.
- Option 1: (Here, you are just repeating the previous part for Step 7, just for your next expense).
- I can click in cell D3.
- Type “=”,
- Then click on cell B3,
- Type “-“,
- Then click on cell C2,
- And click enter.
- Cell D3 should say “$0.00”. (If you do the math in your head “$50-$55 it equals -$5; therefore you know you got the right answer.)
- Option 2: Remember how I said I like to combine steps to make it take less time? This is another excel trick. Let the program do its job! Excel is supposed to be helpful. So, instead of doing each individual expense and creating your own formulas, you have already created the 1 formula that you need. Now let’s use this formula to “auto-populate” the rest of my expenses. How?
- Click in cell D2. A black box outlines cell D2. Notice at the bottom right corner of cell D2 there is a little box on the corner?
- Click and hold on this box, aka click and hold on the bottom right corner of cell D2 and drag your cursor down to cell D6 and let go of your mouse.
YOU DID IT! 🙂 Each expense has a projected cost, actual cost, and cost difference in dollar format.
Please comment and leave any questions you might have whether about this small excel piece or further for future blogs.