Primer: First Steps
Excel (and other spreadsheet providers like Google Sheets, which most of these demo videos use) gives you a set of prebuilt structures and actions that - in theory - help you organise and analyse data to make better decisions.
Sounds… almost reasonable.
But.
To be useful, Excel makes assumptions about how you think, and how you’ll use the program. And if you don’t behave in the way Excel is built to handle… well… at best it doesn’t make sense, at worst it gives you wrong results you trust. Like that 5th beer.
To maximize Excel’s use with minimal faff, work with it as much as possible and take shortcuts wherever it can’t keep up with your needs.
It’s easiest to do this if you understand the various components of Excel first - what they are and how they work - which we’ll use to ‘do stuff’. I’ll break this down.
Break down: Navigation
Excel is made up of workbooks (which are individual files in your file explorer), which can have multiple sheets organised in tabs, each of which has multiple cells. Groups of cells are called a range. Multiple crows are a murder, multiple cells, a range. Go figure.
Most actions involve ranges. Say you want to copy/paste some data - you’ll need to tell Excel what you want to copy/paste, so you select it.
​
FYI none of these videos have sound so you can play them at will wherever you are. I take no responsibility for sound from other tabs though...
You can also do this with words - aka formulas - which we’ll cover later.
Now let’s look at a single cell and what can be put inside it.
Break down: Data Types
Excel has built in instructions about certain data types including:
Dates
Knows a lot
Dates can be put in order - January comes before March, but January 10th, 2024 is after March 3rd 2023. Dates have a day, month and year, there are a certain number of days in a year, days in a week, weeks in the year, and similar. Think of anything you learned in kindergarten about dates and that's about where Excel is.
Numbers
Knows all
Numbers have an order to them, similar to dates (for example 2 > 1) and there are arithmetic operations like adding, subtracting, multiplying and dividing that are performed with numbers in preset ways. Two times two will always be four. Excel has additional function packs for more complex operations including linear regressions and other statistical modelling.
Text
Knows little
This is where Excel's function is limited. It knows superficial things about text; how many characters there are, how to alphabetise; it knows 'Apple' starts with an A but it doesn't know it's a fruit nor what colors apples can be. Think of Excel understanding text like you understand the Rosetta Stone; you can describe what is on there, you could add characters to it but you have no idea what it means.
Red Flag
When what you see is not what you get
What you see in a cell is a combination of what data is in there, adjusted by whatever formatting has been applied to the cell.
​
Sort of like Photoshopped photos: Photo + filter/editing = what you see
In Excel: Data + formatting = what you see
​
Why does this matter? Excel requires you to be precise as it won't be able to apply reasoning when your instructions clash with its reality.
Dates
These two cells for humans represent the same thing; a date, and specifically the 15th of March 2024. However... ​
Now with one cell, who cares? Just put slashes instead of full stops and move on.
Sure. And when your data set has 1,000 dates in it that Excel doesn't recognise and can't order from oldest to newest to make a chart? Yikes. ​​
There are a few ways around the date issue depending on what spreadsheet system you're using and frankly how it's feeling that day. You could do a find and replace all, substituting a / for a . by following the instructions in the prompt. In Excel for Windows, Ctrl F will pull up the dialog box. If this doesn't force Excel to recognise the date format, you can also use the DATE() function. This will make more sense once you've gone through the entire primer so if it's not making sense come back at the end. If your data is in column A, your DATE formula would look something like =DATE(right(A2,4),mid(a2,4,2),left(A2,2))
​Text
Another common issue is when your cell contains more data than you think it does; usually this happens if you have extra spaces, most commonly at the end (often called 'trailing spaces'). If you are performing checks and want to see if the contents of one cell are identical to another, Excel may give you unexpected results and say they aren't, even if you, or the data provider intended them to be. 'Amelia' is not equal to 'Amelia ' because of the extra space at the end.
Breakdown: Built to try to be helpful
So now you have some idea of the big picture of how Excel is structured, and the little picture of how it understands what is put into cells. Now we will add another layer which is Excel’s predictive function. Despite all evidence to the contrary, Excel wasn’t designed to piss people off - but just as when someone tries to complete your sentence and misses completely what you’re going to say, so does Excel attempt to guess what you want... and results vary.
Tables are similarly designed to anticipate what you may want to do with your data. If you choose to call your data range a table, Excel should - emphasis on should - include data you add to the end of it, or expand the table when you add a column, and ought to drag down your formulas through the entire table. You may or may not want this to happen so just be aware of it for now.
Building Up: Removing ambiguity with specific instructions, aka filters and formulas
So far we’ve looked at how Excel is built - sheets, cells, ranges, what types of data you can store in cells and what Excel tries to do for you when what you are doing is - in its opinion - ambiguous.
What isn't ambiguous is formulas, and the result of those formulas. Think of formulas, and their closely related cousins logical expressions as instructions. These instructions can be written inside a cell, or they can be used in other areas like filtering to tell Excel what it needs to do. Some instructions Excel already knows, like those used in basic math (remember in math class there was an order to calculating a multi step equation - PEMDAS, for reference, so that no matter who did it, they would arrive at the same answer? Yea, that one).
Here's an example of a formula written in cell B2. It tells Excel the instruction for B2 is that B2 should display whatever number is written in A1 plus 2. If A1 is 10, B2 would display 12.
=A1+2
Why does anyone care about this? Because through formulas, logical expressions and filters (again, these are all sets of instructions for all intents and purposes) you can evaluate large data sets, make repetitive work faster and less error prone, and focus your time and energy on what is most important to you or to your business. Let's look at two examples in a bakery business:
​
1. Take a multi criteria pattern and apply it to a large set of data
Let's say in your bakery you want to send out a discount voucher to your biggest revenue customers during August, but only those who live within a 5 mile radius of your store, and favouring those who haven't shopped this year yet.
Your customer data comes out looking like this from your order management system (or, frankly, you're able to copy/paste and edit it to the information you need) - and this video also demonstrates another delightful (or annoying) feature of Excel; the ability to come to the same conclusion in many different ways. Which is the best depends on what you need to do with the data in the future. Don't worry, we only go through two variations in this particular scenario.
​
Personally I would use the 2nd version as the criteria then are visible instead hidden in the column filters but if you're just doing a quick back of the envelope calculation you may not want to bother with a multi criteria formula.
Click here to download the workbook from the video. Yellow cells have formulas, green cells are where users would normally input data.
2. ​​Make changes to existing work without needing it all to be amended or redone.
Let's say for example that you own a bakery and need to calculate payroll each month for your 5 employees. They are each paid a different hourly rate, and Saturdays anyone who works gets paid 10% extra per hour. You could manually (bust out a calculator for this one...) calculate this each week - but how many times have you pressed the wrong @(£*%@& button on a calculator and have had to start again?
Instead consider making a sheet like this, where if you need to make a change or an update you can immediately see the results. This also allows you to easily do scenario analysis - what happens if I increase everyone's pay by x%? What happens to my profit if my landlord increases my business's rent by £300/month (and if you think your profit goes down by £300, please speak to a tax accountant who can help you learn whether you can write off more of your business costs so these types of changes hurt your profit less...)?
Click here to download the workbook from the video. Yellow cells have formulas, green cells are where users would normally input data.
Explainer: How to specify what Excel does when you move a formula​​
​
You'll notice that there were a few types of formulas used in those examples, some in combination with the fill function. Autofilling formulas is more involved than filling with plain data, because Excel gives you a bunch of options to specify exactly what you want done when the formula is moved (filled).
There are absolute references, variable column reference, variable row reference and full variable. Let’s take each in turn with some examples. Note that on a Windows machine on the desktop version you can use F4 to toggle through instead of putting the dollar signs in manually. Google Sheets also allows some shortcuts though they will differ depending on your setup. With Mac in Chrome with a keyboard that has the function keys, it's fn F4.
Building Up: non arithmetic
instructions in formulas
We’ve looked at a few formulas that use arithmetic expressions (greater than, less than) on different combinations of cells, and now we’ll look at adding criteria to that.
This example will stick with SUM but the concept applies to other functions like averaging. In our bakery example, we want to figure out how much we sell of certain items, and how much each location sells of different categories of pastries of goods. These then can become inputs or building blocks of models that answer questions like whether a central kitchen is better than two or more kitchens closer either in or close to the storefront (or mobile cart), whether discontinuing certain products will increase profitability, and more.
​
Here we use SUMIF and SUMIFs to get the data we're looking for (and yes - like the examples above it is possible to achieve the same results using different Excel tools like Pivot Tables - but if you understand these basics it makes everything built on top of them comprehensible. Like math - now you use a calculator with abandon but to do so and get useful results you needed to learn how to do math without it first).
Click here to download the workbook from the video. Yellow cells have formulas, green cells are where users would normally input data.
Build Up: When data
isn't conveniently presented
(aka a normal Tuesday)
We’ve looked at a few straightforward formulas that use data that is already in the right place (or that is easy to input into the right place) and the right format and now we’ll move onto getting the data into the right place with a VLOOKUP.
VLOOKUPs for whatever reason have been used as an arbitrary line between people who ‘know’ Excel and people who ‘don’t’.
That's crap.
VLOOKUP has acquired an undeserved reputation as a difficult/complicated demon beast when in reality all it is doing is bringing back data you want from another place. You already do plenty of real life vlookups - if you can manage to go to the grocery store and, when told the tomato sauce is in aisle 2, go and get some and bring it home for dinner, you can do a vlookup. Same action, just with pixels instead of groceries.
In this example, you want to understand whether having two different types of pastry dough is worth it, or if cutting down to just one would increase profitability.
Click here to download the workbook from the video. Yellow cells have formulas, green cells are where users would normally input data.
Build Up: How complex can you get?
​
Excel has the ability to perform as complex or as complicated a set of instructions as you are willing to write down in unambiguous formulas that consider all possible outcomes. Complex instruction fails most often - or produces unusable results - when the user hasn't specified what should happen in an outlying or otherwise unexpected data situation. This could be anything from more rows to a different category or element, or even an error that is expected, and has a specific meaning.
​
The example in this video shows a rudimentary bakery model ('model' means organised data that can be mucked around with to see what effect a certain change, say an increase in minimum wage, has on the business) that has some example costs and revenues along with bottom line profitability*.
​
This model then is tested when a new product is introduced, which comes with new ingredients, and is inserted randomly into the dataset.
Chaos ensues.
*Excludes any guess at taxes owed
Click here to download the workbook from the video. Yellow cells have formulas, green cells are where users would normally input data.
See if you can - given what you learned - amend the formulas in the model to take into account the cherry cake revenue (and associated costs!)
Primer Wrap Up: The junk drawer
My husband petitioned to have this list included of what he finds most useful, and what he hates, as a 'non Excel' person*:
​
Useful
​
-
Freeze panes - basically the cells of your choice 'freeze' or are always visible as you scroll.
-
Formula tooltips - in theory, useful. In practice, requires some googling (or memorising Excel lingo)
Why the f*ck haven't we solved these yet:
​
-
Windows vs Mac vs Sheets - like ex girlfriends, better the twain never meet. Ranked from most functional to least: Windows based Excel > Mac > Google Sheets
​​
-
Printing on Windows Excel is fine. Printing on Excel for Mac is heinous.
-
If you must print, regardless of software, best practice is:
-
Repeat headers on each page
-
Page numbers
-
Min font size is functional size 7, ideally 10. Functional means if you have size 7 but then scale to fit columns on one page, it may shrink the font too small.
-
Font size - don't go under (functional size 7). Usually size 10 is the better option. Particularly useful for invoices that may need printing.
-
Know your audience though - if it is mostly people with bifocals, keep it 10 or larger. ​
-
-
-
​​
-
Excel 365 won’t necessarily be compatible with Excel 2013... like hey this spreadsheet from 2013 will definitely work and.... Nope. Back compatibility is always a problem (e.g. XLOOKUP only works in 2021 or later) but Excel delightfully has put in some forward compatibility issues as well. Assume that whenever you receive a spreadsheet not done on your own particular version it’ll involve pain and suffering.
​​
-
3 dimensional data in Excel - please don’t. What does this mean? Let’s say you have a table broken down by SKUs as columns and by client as rows. I call this 3D data because if you want to do anything useful, like add up all of the gardening item sales by the client on Rosemary Street, you can’t do so simply. You’d need to not only have the correct row and column but you’d need to have all of the different columns for Gardening items.
​
*Everyone is an Excel person, some just use it more than others.
Learned something? Leave a tip!
Help keep this course free by leaving a tip if you found the content on this page helpful, timesaving, nice to know, funny, or otherwise feel it is worth keeping online. The average contributor pitches in £5 - £10 per module, and if you need an invoice or receipt to submit to your company for continuing professional development drop me a line and I'll sort it out.