For an Excel virgin, opening the program is about as scary as it gets. Endless boxes awaiting complex formulas, and not a clue where to begin!
Your task: design a spreadsheet which calculates how many of X item we need to order for our 8 branches at the end of each month.
Your response: ARGH.
Follow this step by step guide, and learn the tricks of Excel.
TIP #1: Start simple.Type out your basic structure. You’re looking at how many items are sold by each branch in order to know how many need replacing – So type out each branch name. The other factor in this equation is time – you’re looking at a month, so type out each week. It should look something like this:
Step 2: Introduce numbers. How many of this item should each branch carry? For this example we’ll say 10.
Row 10 (where along the left hand side the numbers are listed) will be a total of how many units have been sold across all 8 branches. To formulate this, click on box B10 (where ‘B’ is listed at the top of the spreadsheet and meets with the 10th row). In this box type: =SUM(B2:B9) – this formula adds together the values in cells from B2 along to B9. If you had more branches, you would adjust the numbers in the formula to fit the equation.
I choose box 10 because it makes for a neat layout – but you could put it wherever you want and achieve the same results. Type something for reference in A10 – such as ‘sold’, as in my example.
Follow these steps for cells C10, D10 and E10. Your table will look like this:
TIP #2: You can find the formulas by clicking the Fx symbol after clicking on the cell in question. It appears above the top line of cells, next to a cross, a tick and the formula bar. It looks like this: and will help you find the formula for pretty much any sum.
Step 3: Now you’ve got the basic spreadsheet working (test out putting numbers in the cells to see if they work!) it is time to start totaling the amounts, and showing how many items need ordering.
Choose a cell to display the totals. I’ve chosen F12 + F13 – We need one to show the total ‘to order’ and another to show the total ‘In Stock’. Type both subjects into E12 and E13, leaving F12 and F13 clear for the sum.
To calculate order total, you know that each branch needs 10 per week, and there are 8 branches – therefore you’ll need to keep 320 in stock per month. The basic sum is 320 minus total sold. In F12, you’ll want to add up the values in B10, C10, D10 and E10 to show how many items you need to order. Do you remember how?
You can either type =SUM(B10:E10) OR =SUM(B10,C10,D10,E10) It is up to you, they both work the same.
In F13, we’re displaying how many items you have in stock. This should be stock total ( 320 units) minus how many have sold (in F12) – try using the Fx button to find the right function for this equation before reading on.
The correct function is IMSUB, so the formula should be =IMSUB(320, F12) typing (f12:320) would display a minus number. So far you spreadsheet should look like this – if you’ve entered sample numbers in to the cells.
TIP #3 – Formulas that use cell numbers will update when a corresponding cell changes – without you having to do anything. If you type numbers into any cell, you’ll see the spreadsheet at work!
Step 4 – Designing the spreadsheet isn’t necessary, but if you were to create a spreadsheet for a presentation, or just because you wanted to, it’s simple and great to know how. You can use the text, highlighting, text size, font colour etc to any of the information on the spreadsheet. If you’ve used Microsoft Word before, then you already have all the knowledge! To colour a row, just highlight and click the fill button.
Tip 4: Realised that you should have put something above the formulas? Company logo perhaps? Fear not! Highlight the entire thing, and select copy (either CTRL + C, or right click and select ‘copy’). Now, (without deleting anything!) select where you’d like the cells to move to. Right click, select ‘Insert copied cells’ and your formulas will have moved. You now will have room to insert more information! You can now delete the original cells (highlighted areas will need to be removed by selecting ‘no fill’ where you selected the colour previously)
To add a logo, select a cell, move to the ‘insert’ tab at the top of the program, click the photo icon, and select your picture.
And there you go – a fully functioning, well designed basic spreadsheet! Why not try making a few up of your own? MS Office skills are crucial in most administrative positions, and a good working knowledge is great to put on your CV. Play with different formulas, different layouts and try and make something really complex. Good luck!