So far we have carried out a nice bit of work in Excel: loading and manipulating data through PowerQuery; formatting through pivot tables; data analysis and soon visualisation through PowerPivot (maybe some DAX here if you were feeling adventurous!). All of which has occurred within any complicated Excel formulas.
Unfortunately we will soon start to look at a few ways to use formulas in Excel to carry out analysis or work some magic on your data. First, I want to go through some aspects that will make the formulas more understandable if you haven’t come across them previously. Over the some of the next posts we will cover a variety of these in nice, short posts.
First up, using references in Excel. Every cell in an excel sheet has a cell reference based off column row and number. e.g. the top left cell in a sheet is A1. You can see what the cell number of your selected cell is by looking at to the left of the formula space to the name box. Excel will also highlight the column along the top and row along the side of of your selected cell at the side of your table.
We can make a cell equal to the value in another by typing equal to(=) and select the desired cell OR typing
the desired cell. If we then highlight the cell with the formula on it, the bottom right of the cell should be a thick square. By selecting and dragging that square it moves the formula into adjacent cells. However by doing this it will change the cell the formula references also. If the reference changes as you move it is considered a relative reference.
However, we can stop the cell reference changing if we wish by making it an absolute reference We have 3 options when it comes to absolute references:
- Full Absolute: =$A$1. If we try to drag this reference into other cells it stay completely the same
- Partial Absolute Columns: =$A4. If we try to drag this reference into other cells the rows will change but the columns will always stay the same
- Partial Absolute Rows: =A$4. If we try to drag this reference into other cells the columns will change but the rows will always stay the same
Shortcut for changing reference type in Windows is ALT+F4, Mac is CMD+T. The order the reference changes as you press the hotkey is: A1 --> $A$1 --> A$1-- > $A1-- > A1
There was a quick introduction to cell references in Excel, using cell references can quickly allow you move data or formulas from one cell to multiple cells.
SportSciData 