One of the popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task. (Includes example spreadsheet and video.)
Below is a list of various Microsoft Excel tutorials that we've done. These include step by step instructions. In some cases, there may be sample spreadsheets or video screencasts. This section is several pages long so be sure to use the blue Next Page >> button at the bottom of the search box to the right.
I've also been updating these articles to reflect the latest Excel version. Presently, I'm using Microsoft Office 365. However, you may see some tutorials that reflect older versions. All the screen snaps have been done on a Windows computer and reflect those commands.
If you don't have Microsoft Excel, there are three options:
- Get an Office 365 (Subscription service)
- Buy Microsoft Office 2019 (One time purchase)
- Use the free version in your browser
The free version does require you to have a Microsoft account. As you might expect of a free product, there are some limitations. For example, you can open a workbook that has macros, but you won't be able to edit them.
You can read more about the different options on the Microsoft site.
It should also be noted that Office 365 releases new features on a monthly basis and even sooner if you're in the Insiders program. In some cases, these new features may not make it to older standalone versions. An example of this is the new XLOOKUP function.
Our most popular Excel Tutorials
The problem with many Excel spreadsheets is they make the reader find the key information rather than using Excel to highlight these cells. Fortunately, Excel’s conditional formatting can make your data automatically stand out.
Do you ever play the “what-if” game when you see data? For example, how many more widgets would we need to sell to get our 20% bonus. In my case, I was trying to figure out many more voters we needed to win. Excel has a simple feature for these type of questions.
Everyone looks at information in a slightly different way. Some people create Excel spreadsheets where the data is useful, but it doesn’t work with the way you view or interact with the data. One common example is where you want to transpose or switch Excel row or column data so Row A data becomes Column A data and so on.
Recently, I volunteered to help a friend on a project which required delivering a weekly Excel summary. When I asked my friend how the spreadsheet looked, he hesitated. He looked me in the eyes and said, “It’s too complicated”. My immediate reaction was disappointment. I knew the one I produced was simpler, but I didn’t know what “too complicated” meant. It turns out the data was fine. I simply needed to show him how to freeze the Excel columns to make scrolling easier.
Like many people, I have a love-hate relationship with software. It’s great when it works, but it’s so frustrating when you get a cryptic error message. And these messages seem to appear when you’re short on time and patience
Recently, a friend asked about fixing an Excel spreadsheet where the subscriber name was in 1 column. He wanted the name separated so he could send a personalized mailing. This is not the first time this question has arisen. The key to solving this problem is to parse or split the data using the Text to Columns feature.
Excel allows you to sort your columns and rows by lots of ways. One less obvious way is to sort items by color. This is handy if you apply a background color to a cell or different font color. This tutorial explains how to do the sort. You can also filter by color.
Earlier this month, I got a request from a manager in town asking if I could randomly select 150 people from a membership list. No darts allowed. I think the purpose was to do a survey, but maybe it was to select contest winners. It turns out that Microsoft Excel has a random number generator which was the key to my solution.
This Excel tutorial is the follow up to the previous article we did on using Excel’s built-in macro recorder. It will introduce you to the macro editor and layout so you can create or edit your own macros. It’s also a great way to learn how the macros work.