Excel Tips

Here are some great tips from Kaylee Baldwin (student in ACCT11059):

Hi Students!

I’ve noticed a few of you are new to Microsoft Excel, and that some of you who are familiar are still learning the ways of the spreadsheet. I thought I might post a blog with some hints and tips and some links that may help you when fiddling with your Financials Statements and also just in general!

I’ve started with the basic summing function so just skim past this if you are already a guru in the summing department!

  1. Formulas 
    Excel has so many formulas options that I haven’t even learnt them all yet! From simple maths to logistics! Excel seems to be something you can learn as you go depending on what you are trying to do but never think you will have to do anything the hard way! For any math or totals you want calculated you can do the following;
  • In the cell (box) you want the total type =SUM(
  • Select the cell you want to include then use your math symbol (+, -, /, *) Where / is divide and * is multiple
  • Select another cell and repeat the process and close your bracket at the end!

If you have a whole row of cells you want added together you can simply type =SUM( then click the top cell from that list and drag down to the last cell and close your bracket at the end.

Now the markers would reallllyyyy like it if we linked where we are pulling data from (and remember supporting evidence can give you part marks EVEN if the total itself is incorrect). Same concept as the summing here but just type = in the cell you are putting the data into then select the cell you are copying (this can be from another sheet in your spreadsheet even!) Maria will cover this in her video which is on the Echo site dated 19 April.

**If you get an error on your sum just check you have included your brackets in your formulas, sometimes I forget!

  1. Autofill

If you want to create continuous cell values or formulas you can use Autofill. This will copy the data from a cell down into however many cells you want! This can be helpful if you are trying to create continuous dates along rows or columns, copy the same thing many times in a row, list numbers down the column… To infinity and beyond! It also helps in the assessment if you wish to drag down your link formulas (Maria explains this in her video but also explains why she doesn’t like it which I have shed light on for her and will explain shortly)

I’ve included the link if you click on autofill above, simply because it lists some options step by step including different versions of word and how to turn it on, so rather than me blabbering on you can have a look at that specifically.

Maria also mentioned in her video that she didn’t like to drag and drop because the autofill will remove her pretty lines (and when you spend some time making it pretty this can be super frustrating, trust me!) A little tip I taught myself is that you can activate a little options box to pop up at the bottom right of what you filled (only right after you’ve done it not later) it looks like this  . Click on that and you can select ‘Fill without Formatting’ and voila – pretty colours return!

  1. Hyperlinks 

I think we have all learnt about hyperlinks from the assessment in Word but did you know they work in Excel to! (It is brilliant!) I have utilized this in my Financial Statements Spreadsheet in the Documentation section at the top right I have listed my reports for easy reference.

For example – Type Annual Report 2016 then right click on this cell to bring up options. At the bottom of this there is a Hyperlink option!  This gives you many options in itself you can link it to another sheet, a document in your folders, or simply paste your web address into the ‘address’ box and hit OK. It should turn blue like in Word.

**As an example in the real world – I have a spreadsheet to keep track of stock for the processing plant I work for. We have to keep extensive records for audits including dates and records for packaging/ingredients received. When the goods come in I scan and save the docket to a locked folder and hyperlink this to my spreadsheet where I recorded the incoming goods so I can just click and open when I need it rather than finding the date and correct product in my spreadsheet then looking through my folders for the docket.

Now I could go on for a million years here but the point is, I love Excel and these are just 3 things that will help in your assessment for this course. So please, if you get stuck or think there might be an easier way to do something fiddly don’t be afraid to ask because chances are; Excel will do it, and someone will know how!

https://blog.udemy.com/excel-formulas/ – Some more tips.

Good luck to all – and remember we are all doing this together so never be afraid to ask! I am hoping to have my draft spreadsheet up next week once I have completed my Restated Financial Position. So feel free to pop in and check it out it might help with formulas and formatting.

Thanks, Kaylee

Some great tips and also links to further tips.

Martin