How do you use VLOOKUP with multiple lookup criteria? In its basic form, VLOOKUP can only look up data based on one criteria. To get around this, you could add in another helper column that concatenates the different criteria together and then looks up data based on that. You formula might look something like this. Using array formula plus the CHOOSE function we can avoid this workaround and keep our data tables less cluttered....

A serial number is a unique identifier for a row or record of data and they will usually start at 1 and increase incrementally with each row. This way you can refer to each record in your data set by the serial number. In this post, I’ll show you 15 interesting ways which you can add row numbers to your data. Use the Row Headings Good news! Excel comes with serial numbers out of the box....

Often you will need to rearrange your data when designing your spreadsheet solutions. You might need to move old data out of the way to make way for a new entry. Shifting cells down allows you to create space in your workbook and move any existing cells downwards so you don’t end up overwriting them. This post will show you all the ways to shift cells down in Excel....

Range A range is simply any collection of cells. Workbook A workbook is simply another name for your Excel file. Worksheet A worksheet is where Excel stores all your text, numbers and formulas. Each workbook contains at least one worksheets but can contain as many as you need subject to your computers memory restraints. Ribbon The ribbon is Excels graphical menu interface for commands you can perform. Commands are organised into different tabs and groups within tabs....

Note: When using the completed workbook, you will need to change the folder reference in the query to wherever you save the sample files. From the Query Editor, go to View > Advanced Editor and change the folder path. If you’ve ever come across a situation where you’ve had multiple files of data with each file having data spread across multiple sheets then you’ll want to read on. This post is going to explore how to use the From Folder Power Query to import multiple files with multiple sheets in each file and aggregate the data into one table....

For this you’re going to need to be familiar with what we did in the previous post as this post takes off where that one left off. So have a read of this first. How To Import All Files In A Folder With Power Query Step 1: Create a query to get the named range. Firstly create a named range to reference. I have called my named range FilePath and copied in a new folder location....

Get The Completed Workbook You could do this in a number of different ways but these are the two that make the most sense given a column of data comprised of small blocks of related data like in the example. In this example every three rows of the column relate to one person. Option 1 To create a table where each column contains related data we can use this formula....

A column is a vertical group of cells in your spreadsheet. Columns help you to organize your data and a column will usually contain the same type of data across all the cells in the column. Inserting columns for new data is a very common task in Excel. Columns can be used to add extra information to your spreadsheet with data entry or calculations. Inserting columns is easy to do in Excel and there are many ways to get it done....

The Ribbon is Excel’s command menu interface. It organizes commonly used actions together in an intuitive and visual way. These are the main parts of the Ribbon. What Tabs Are In The Ribbon? There are 7 Tabs in Excel’s default setup. In addition to these 7 Tabs, there is an 8th one called the Developer tab that is hidden by default. You can read this post to find out How To Enable The Developer Tab....

In this post we’re going to make a pizza pie… chart. Not as delicious as real pizza, but this is a cool trick to jazz up your charts and involves changing the fill option to a picture for your various data points. This trick works for other charts like bar charts and you don’t have to fill them with a pizza picture, you can use something more relevant to your data, so if you’re an office supply company and you wanted to make a graph showing your top 5 products you could use pictures of pens, pencils, staples, paperclips and paper....

Highlight a column in the data and open the Go To window. You can also use the Ctrl + G keyboard shortcut to access the Go To window. Now only the blank cells in the column you highlighted will be selected. Delete these rows. A similar trick will work for blank columns, just select Entire Column instead of Entire Row. Voila! You just saved yourself a tonne of tedious work....

What’s in the data? Sales Representative – 5 different sales representatives including 3 in the East and 2 in the West.Location – State the sale occurred in. 8 Different States in the data.Region – Region the sale occurred in, either East or West. 4 States in the East and 4 States in the West.Customer – Customer’s name on the order. There are 530 different customers in this data.Order Date – Date the order was made by the customer....

Yep, Excel can do pretty much everything, and that now includes sending texts. But here’s the deal. This is going to cost a little bit of money unless you just want to send texts to yourself, in which case you can use a trial account. But it’s actually probably cheaper than most pay as you go phone plans for texting. The whole setup is going to use Microsoft’s Flow Excel add-in and Twilio....

You might need to count the number of words in your text data for many reasons. Perhaps you have comma-separated lists and need to count the number of items in each list. Unfortunately, Excel doesn’t have a built-in method for counting words. But there are a few clever ways to get the result you need. This post will show you how you can count how many words are in your text data in Excel....

Yes or No is a common replacement for the Boolean data type where the Yes value is interpreted as true and the No value is interpreted as false. This can make the information in your Excel dataset easier to read and interpret the meaning for your users. This post will show you all the ways to create a yes or no in Excel. Add Yes or No with a Dropdown List The best way to add a yes or no value to your workbook is by using a dropdown list....

This is a pretty popular way to display a fundraising campaign’s current raised amount versus their goal or target amount. Let’s look at how we can build this thermometer visual from scratch in Excel (and PowerPoint). Video Tutorial Creating A Thermometer Template In PowerPoint We’ll need to head over to PowerPoint to create our thermometer template. As I explained in my traffic light dashboard visual post, PowerPoint has some extra merge shape commands we need to use to combine and cut out our thermometer template....

Ok, by “top secret“, I actually mean a database API made publicly available. And by “hack into“, I actually mean use Excel’s From Web Power Query functionality to access the API. Governments have a lot of data covering agriculture to renewable energy, and the cool thing is anyone can access it and they provide it all for free with an API. Data is always a valuable resource to have at hand when you’re trying to learn Excel too, so learning how to fish for your own data is definitely worthwhile....

Let’s say you have sales data for your company and you need to send each of the sales representatives in the company a copy of their sales. You might not want to share the entire set of data with each rep but just their own sales due to privacy concerns around their commission based compensation. To do this, you will need to take our original set of data and parse it out into many different workbooks (one for each sales rep) based on the sales rep column in the data....

If you want to view your performance versus a target for some metric like sales then a thermometer style chart is a great way to do this. In this example we have a table with columns for the sales person, their target sales and the actual sales they had. We will create a 2D bar chart for this data. Add your performance data to the secondary axis (in this case the sales data)....