Excel Tips
Here are some tips to get you started using functions in Excel.
Start from video 1 and work through or just pick the one you need.
1. Plus, minus, multiply and divide
In this first video we are going to look at using Excel for simple mathematical operations
2. The SUM function
In this video I’m going to show you the SUM function. It’s both simple and important, adding up numbers is probably the thing we most expect a spreadsheet to do for us.
Useful to know
• The result of this function will be a number, because you’re adding up numbers.
• You only need to select one range of cells. But as you’ll see from the video you can add up numbers from different parts of your spreadsheet.
3. The IF function
In this video I want to show you the IF function. I use this A LOT and it can do some really powerful things.
This one has got a number of different pieces of information we need to fill in.
• Firstly we set up a test that will give us a TRUE or FALSE answer
• Next, we tell Excel what to do if the result is TRUE
• Finally we tell Excel what to do if the result is FALSE
The sort of things we can tell Excel to do for us might be to show us some text, do a calculation, show us a date etc. Whatever you want, really. Check out the video for a couple of examples.
In my previous video, when we looked at the SUM function, I mentioned that the answer we got would be a number. For the IF function, it could be anything, it depends on what we asked Excel to do for us.
4. The VLOOKUP function
Like the IF function video, the result that we can get from VLOOKUP could be anything – a number, text, a date etc. It just depends on what we ask Excel to give us as the answer. It could be an error value, if what we’re looking for isn’t there and I’ll show you that in the video.
5. The COUNT function
This video is about the COUNT function, which counts how many cells have numbers in them. Over the next few videos I’m going to take a look at various function that help us count things.
6. The COUNTA function
In the last video I showed you a function called COUNT which counts cells with numbers in. In this video I’m going to show you a function called COUNTA which counts cells which are not empty, so that includes counting cells with text in too.
The result you get from both COUNT and COUNTA functions are numbers, because they each are the total of the counting exercise.
7. The COUNTIF function
We’ve got another counting function this time, COUNTIF, if you haven’t seen it before I think you might find it useful.
We can use COUNTIF to give us the total of certain items in our list, depending on what we ask Excel to count.
8. The COUNTIFS function
Yet another counting function which, is similar to COUNTIF in my last video but is even cleverer. It’s called COUNTIFS and it will let us count items in a list of data that meet more than one criteria. This is great for helping us understand segments in our data.
9. The SUMIFS function
Over the last few videos we’ve seen some function that have helped us count things, most recently where the things we’ve wanted to count have met a specific criteria.
In this video I’m going to show you a function that will add up numbers depending on our criteria.
The example I’m going to show will look at a list with lots of different items and how many have been sold and will give us the total for each of those items, without us having to do some awkward dance where we manually choose the particular numbers we want to add up.
This way is a lot quicker to set up and is much more reliable.
10. The SUMIFS function part 2
In the previous video I showed you a function called SUMIFS, which added up numbers that matched a criteria, which happened to be a SKU code that uniquely identified a product.
Of course, it could have been anything, such as a name for example.
This time I am going to take the same function a little further but showing you how to use SUMIFS with two criteria, so we can easily see even more detail in our data.
This is a great function to help us see what’s going on in our businesses, by understanding the data.
One thing I love is that, once you’ve set your sheet up, if you add more data to the bottom of it the totals change just like that.
11. An introduction to dates in Excel
Dates matter in business.
Whether you realise it or not, your business is collecting information about what is happening over time and that information can help you understand what is working and what is not so you can do more, less or different.
Knowing how to work with dates in Excel is powerful. But that doesn’t mean it’s difficult.
I’m going to show you more over the next few videos.
Did you know that dates in Excel are really just numbers presented in a recognisable date format?
If you didn’t, check out my video and I will show you.
There’s also a simple tip if you need a list of dates.
12. The TODAY function
This function is called TODAY and you can use it to always display the current date in your spreadsheet. Great for comparing to a deadline date and seeing how looming it is! And in fact I will show you that in more detail soon.
13. The NOW function
This function is similar to the TODAY function.
This one is called NOW and the difference between TODAY and NOW is that NOW shows the time as well as the date.
14. Relative and absolute cell references
I’m not showing you a function in this video but I am going to recap relative cell references (which we’ve used up until now, even though you may not have known it!) and introduce you to absolute references.
Knowing that these two options are available, how they differ and when to use each can make our spreadsheets easier to use and update, and keep error free. Especially months later when we’ve forgotten how we built them!
15. Using the TODAY function
I have previously shown you the TODAY functions and promised you that I would show you how we can use it. That’s what today’s video is about.
We’re going to use the fact that Excel always know what the current date is to help us figure out how long we have to complete tasks on our list, based on their due date.
Or perhaps how overdue they are!
We’re also using what we learned about absolute references last time and there’s even an IF function thrown in for good measure.
How can you resist? 😁
Excel is my go-to tool for keeping organised, these are some of the things I use it for.
16. The WEEKDAY function
The function in this video is called WEEKDAY.
It’s a great one for helping make more sense of a big list of dates because it will tell us which day of the week a date is and we can use that information to compare performance on different days.
The answer given by the function (called the result) is actually a number rather than Monday, Tuesday etc but I am going to show you next time how we can get the names of the weekdays rather than a not-so-helpful number.
17. The CHOOSE function
In this video we’re looking at a function called CHOOSE.
The first piece of information you need to give it is a number, in this case the day of the week from our WEEKDAY function that I showed you last time.
The remaining information is a series of values from which the function will show one option depending on the index number.
How many values you include in the CHOOSE function will depend on what your index number could be.
Our list will have seven entries, one for each weekday, in order and starting with the first day of the week. From the choices I made in the WEEKDAY function, that will be Monday.
So if the index number is 5, we’ll get the 5th item on the list. Assuming I’ve set it up correctly, that will be Friday.
Much easier to make sense of than just numbers!
18. SUMIFS again and AVERAGEIFS
This time round we’re looking at the data we’ve been working with in the earlier few videos. We’re going to use the data to see which days of the week have the most sales, based on the data we’ve got going back to the beginning of the year.
We’re going to use one of my favourite functions, SUMIFS, which I use a lot, and it’s cousin, AVERAGEIFS, to help us see what’s going on.
And a couple of little extras, I’m going to show you how we can get a list of all the days of the week even if we only type one of them and also freezing some of the rows on our page so we can scroll and still see data that we want to keep our eyes on.
19. The WEEKNUM function
Here we’re going to build on what I showed you in the last video.
We looked at how we could take our daily sales and find out the totals and averages by day.
We’re going to do the same thing in this video but we’re going to look at the sales numbers by week.
So for each week of the year, we’re going to see our total and average sales.
And we’re going to use a function called WEEKNUM that will look at our dates and show which week of the year it belongs to.
And then we’re going to find our totals and averages, but using the week number as the criteria we’re looking for.
20. The IFERROR function
Last time, we were calculating sales totals and averages for each week in 2022 but we had an ugly error where we didn’t have any data for weeks in the future.
I’m going to show you a function called IFERROR that will help us solve that and also how we can combine our average calculation with our IFERROR function to do all the hard work in one place.
21. The EDATE function
In this video I want to show you a really neat function called EDATE.
Despite the unhelpful name it’s a helpful one to know.
EDATE makes it very simple to find a date that is an exact number of months before or after a date of your choice. Perfect for helping to remind you when you need to review something that’s coming up. Especially when you throw a bit of conditional formatting into the mix.
22. The DAY, MONTH and YEAR function
It’s a triple whammy in this video.
Three functions that all get different bits out of a date.
Need to know the DAY, MONTH or YEAR of a date?
Let me help.
This sort of information on its own can be helpful to look at performance for different months or years. Knowing the day is probably less useful here but I know it’s a function I have used (I just can’t think of an example right now 😬)
Where could these functions help you?
23. The EOMONTH function
This function is called EOMONTH.
Point EOMONTH at a date in Excel and you can get the last day of the month for that date.
This makes it very easy to add up data for the whole month, because you’re looking for one date.
No more having to use “greater than the 1st of the month” and “less than the 28th/29th/30th/31st of the month – delete as applicable”.
(You can also use EOMONTH to find the last day of any month before or after your chosen date, but I think that just confuses matters, so I’m keeping it short and sweet).
24. The DATE function
Here I have a date function for you that will enable us to tell Excel the values to use for year, month and day individually.
It’s called DATE.
It’s a really useful function that I have used lots of times.
And I have got a simple example where we can vary the year we put into the function and the resulting date will set up a list of dates for the whole year.
It’s a really quick and easy way to turn this year’s list into next year’s list, with one quick change.
25. The FIND and SEARCH functions
The next few videos are going to show you some functions for working with text.
Often, it’s really handy to be able to look for text within text, extract bits of text and all sorts of other thrilling things like that.
I bet you can’t contain your excitement for what’s to come 🤣
Well, hold onto your hats because today we are going to start with two very similar functions called FIND and SEARCH.
They both let us look for a letter, word or phrase is present in another piece of text but there’s one important difference, as you will see in the video
26. The LEFT, RIGHT AND MID functions
In this video I’m showing you some more functions that can help us when working with text.
They are the LEFT, RIGHT and MID functions and they help us get portions of text from an existing piece of text.
No surprises for guessing that they can help us pick out text from the left hand end, right hand end and middle of the text we’re examining.
Let me show you how and also explain why these are not the functions you want to use of you are trying to get parts of a date
27. The LEN function
Sometimes it’s helpful to know how long a piece of text is
The LEN function can help us with that.
In the video I’m going to show you how we can find the length of the names of everyone in the England 2022 World Cup Squad
The LEN function is often useful as part of other text functions and I will show you more in 2023.
28. The TEXTSPLIT function
TEXTSPLIT is great for splitting information in one cell into smaller parts – the example I’m using here is to take a name and split it into two parts, first name and last name.
This is the sort of thing I’ve had to do lots of times and TEXTSPLIT, which is a fairly recent addition to Excel, would have saved me a lot of time over the years!
29. Flash Fill and Text to Columns
Excel can help us to break up text by identifying patterns in our data and using those patterns to do the kind of repetitive stuff that we might do manually, saving us the time and the hassle!
Here are a couple of ways we can get Excel to do that boring work for us
30. The TEXTAFTER function
Sometimes we have a list that has information we don’t need on every item in the list.
The TEXTAFTER can help us by looking for a specific piece of text (that could be a character, a word or even a whole phrase) and giving us back just the text that appears after it.
31. The TEXTJOIN function
We’ve seen ways to split up text but what if you need to join it together?
TEXTJOIN is just what you need – let me show you how it works
32. The CONCAT function and &
I’ve got a couple more ways to join text together in this video, after we saw TEXTJOIN last time
TEXTJOIN enables us to join text together with a character or set of characters in between. CONCAT joins our text but with nothing in between.
And then I’ll show you a simple way to join text using the &.
33. The UPPER, LOWER and PROPER functions
Three functions in one video!
They all transform text, either to upper case, lower case or proper case.
What’s proper case? Watch the video to find out
34. The SUBSTITUTE function
This function is called SUBSTITUTE.
No prizes for guessing what it does.
You can use it on whole words or individual letters.
You can even choose which occurrence you want to substitute
35. The TRIM function
This function is very handy when you’ve got text that needs a bit of tidying up
Text that has got random spaces in the wrong places
TRIM has got your back
36. Naming Cells
Here’s something a bit different.
I’m going to show you how to name a cell and use that name in your functions, to make them easier to read.
Very handy, especially when you go back to your spreadsheet six months later and can’t remember how you set it up!
