Fonts in Excel
Uncategorized

How to download fonts and install them in Excel

When you need to improve the looks of a spreadsheet, it’s good to know how to install a font in Excel. Fonts improve the style of documents and web pages. They can also make text more readable and give personality to your projects. There are numerous free and paid fonts available on the Internet. In this article, we’ll dive into where and how to download and install them. Whether you are using Windows or Mac. We’ll show you how to get your new font and use it in Excel.

Sources for downloading fonts for Windows or Mac

You can download both free or paid fonts from the Google Fonts website, the Microsoft Store or other websites.

Google Fonts

There are more than a thousand font families listed on Google fonts. You can search them by language, categories, and font properties. All the fonts are compatible with both Windows and Mac OS. You can even type something, and it will show how it will look in every font, which is quite handy for comparison. All the fonts are released under an open-source license and hence free for non-commercial use and most commercial uses as well.

The Microsoft Store

This one is Windows only. Go to Start, then settings, and choose Personalization. On the left side, you’ll see a button for Fonts. On the Fonts page, click on the link labeled: Get More fonts in Microsoft Store. After you open the store, you will find several individual fonts and font packs for free. However, some fancy fonts are available for a small fee.

You can also find screenshots, descriptions, and system requirements for these files. Click the big blue Get button to download the font file to your system.

Other Sources

There are many other websites where you can download free and paid fonts, for example: FontSpace, DaFont, Creative Market, Behance, FontStruct, Fontasy, FontSquirrel. The list goes on and on.

How to install fonts for Excel on Windows

After you download a font, it goes to the Downloads folder, a folder you specify, or the default folder. Please locate the folder and check if it is a zip file. Unzip the files in a folder and open the folder. Once you see the font file (usually in .ttf or .otf extension), double click the file and select install font. If it prompts to allow the program to make changes and trust the source, click yes. Windows installs the selected font to its operating system files.

If you want to install all fonts at once, choose Ctrl+A to select all files. Then right-click and select install fonts. If a font is already installed, it reinstalls the same font again. Remember, you may need admin access to the system to install new fonts.

You can also drag and drop font files to the Font window in the control panel. Windows automatically installs all those dropped files in the C:\Windows\Fonts folder. If you drag and drop font files even to this folder, Windows installs them as well.

How to install fonts for Excel on a Mac

The procedure on a Mac system is a little different. The Apple operating system installs all fonts via the Font Book app. You need to click the add button in the Font Book toolbar. Then, locate the folder and the font and click open. Alternatively, you can double-click the font file in the finder. It will open the font previewer, and when you click install font, the installed font will appear in the Font Book.

Please note that if you are using Office 2011 for Mac, you may have to drag the font to Windows Office Compatible Collection in your Font Book. Only then the font will be available in MS Office.

Where to find your new fonts in Excel?

After installing your new fonts, they become part of the operating system and are available for use in all apps and software, including MS Office programs like Word and Excel. You can find new fonts in the usual font location inside the program.

Find your font in Excel in the Font command group
Find your font in Excel in the Font command group of the Home tab on the top toolbar.

If you have the Home tab selected in the toolbar at the top of Excel, you can open the font drop-down. There you can see all the fonts alphabetically listed, including the newly installed fonts. Alternatively, you can use the format cells option to open the Font tab. It even shows a preview of all the fonts: old and new. You can also open the font setting with the keyboard shortcut Ctrl+Shift+F. By the way, if you want to learn about more keyboard shortcuts, for both Windows and Mac, check out our Keyboard Shortcuts page.

Conclusion

It is pretty straightforward to download font files of your liking from the Internet. You can quickly install the font files and they are ready for immediate use in Excel. Whether you’re on Windows or on Mac, you can now make your spreadsheets appear more attractive than ever.

Read More
Calculating minus sum? Probably just pi.
Uncategorized

Is there a ‘minus sum’ for spreadsheets?

We have seen people ask this question online quite a bit. The reason is simple, there is a SUM function in Excel. But is there also an opposite of SUM? A formula that will subtract all numbers that you give it? A spreadsheet ‘minus sum’, so to speak?

There are two answers to this question. The first and shortest answer is no. There is no Excel ‘minus sum’. At least, there is not a simple built-in function that will do this for you.

However, there is a way to do this anyway. And the great thing is that it’s quite simple. Let me explain.

Making a spreadsheet minus sum

When you would normally sum multiple values in Excel, you use something like =SUM(A1:A3).

If the values in A1 up to and including A3 contain positive numbers, the result of summing them will be positive as well. Now how do we change this value to be the minus sum? It’s simple. We use =-SUM(A1:A3).

This way, any positive result will be negative. And when you sum negative values you will get a positive result. It will, by definition, be the minus sum of the values you give it.

Learn more

Maybe this was new to you. And you feel like you could benefit from some more Excel tips? If so, you may find our Excel beginner tips and tricks lesson interesting.

Or maybe you feel like you should know more functions to really take your skills to the next level? You can try out the exercises, categorized by difficulty here.

If you have any more tips on minus summing, or opposite type functions in general, let us know in the comments!

Read More
Excel for marketers
Uncategorized

7 Excel Tricks for Marketers

Did you know that Microsoft Excel has over 500 functions at your disposal? These functions make Microsoft Excel useful as an analytical tool for almost any industry. Marketers may not think of Excel as such, but Excel can be a super helpful tool to make their daily analysis of data simpler. Because nowadays, successful marketers need to work smart using relevant data. You simply cannot afford to focus your marketing effort in an area blindly and hope for the best.

In this post, I’ve compiled some Excel pro tips for marketers: the best excel features to make their job easier. Some of the tricks will make you appear professional, while others will improve your ability to analyze large amounts of data. Here are 7 Excel tricks that will improve your work life.

1. Freeze Panes

It is common for marketers to collect a lot of data per day for analysis. By the end of the week, you have so much data that your spreadsheet is long. It can be tough to read out headers when a spreadsheet is long since the headers disappear after a few columns. Therein lays the problem, especially when you are doing a presentation to potential clients.

Freezing panes can be a solution here. You can freeze the first row so that you will be able to see what each column represents. It also makes it easier to explain your data during a meeting without frequent scrolling up and down.

Here’s how to freeze panes in Excel:

Freeze panes in Excel. Select the row below the one you want to freeze, click on View on the Ribbon (the toolbar at the top of Excel) and select Freeze Panes > Freeze Panes.

2. Conditional Formatting

Conditional formatting offers a variety of formatting options for data. You can shade cells, find duplicates, delete duplicates, and other functions for specific conditions. Conditional formatting can come in handy when polishing your data for presentations. As a marketer, you must highlight relevant data and interestingly present your findings.

For example, finding duplicates in sales data can show you repeat customers loyal to your brand. You can remove duplicate data that affects your results, or you can use different colors to identify performing entities from none performing entities. You can play around with conditional formatting until you find functions that suit you.

Here we’re showing every value smaller than 2000 in red:

Conditional formatting, step 1: Select the data you want to format and click on Conditional Formatting on the Ribbon. Choose the formatting you want. We're going with Highlight Cell Rulles > Less than....
Conditional formatting, step 1: Select the data you want to format and click on Conditional Formatting on the Ribbon. Choose the formatting you want. We’re going with Highlight Cell Rulles > Less than….
Conditional formatting, step 2: Define the rule that will decide which cells to format. We're making cells with values less than 2000 red.
Conditional formatting, step 2: Define the rule that will decide which cells to format. We’re making cells with values less than 2000 red.
Conditional formatting, step 3: The result. You now have a nice overview of which months had low earnings.
Conditional formatting, step 3: The result. You now have a nice overview of which months had low earnings.

3. Charts and Graphs

Visual presentation of data is a powerful tool that every marketer should use. Microsoft excel has made it simple to create graphs and charts. You do not need to use a formula to present your data in a graph or chart.

However, you do need to know which data should be shown in the charts and graphs. Additionally, because Excel has various types of charts, you have to pick the one that fits your needs. You can choose to create charts and graphs on a trial and error basis until you find a suitable representation. Alternatively, you can let Excel help you by choosing the Recommended Charts, like shown below:

Inserting a chart, step 1: Select the data you want to visualize.
Inserting a chart, step 2: On the Insert tab on the Ribbon, choose Recommended Charts.
Inserting a chart, step 2: On the Insert tab on the Ribbon, choose Recommended Charts.
Inserting a chart, step 3: The Insert Chart window opens, choose a chart type that fits your data.
Inserting a chart, step 3: The Insert Chart window opens, choose a chart type that fits your data.
Inserting a chart, step 4: The result. A chart is added to your worksheet.
Inserting a chart, step 4: The result. A chart is added to your worksheet.

4. Pivot Tables

Pivot Tables allow you to present a large amount of data in reasonably sized tables. Pivot Tables organize big complex matter into condensed lists and tables ready for consumption. They are often used to help make decisions.

Create a Pivot Table by selecting your data and clicking on the PivotTable button on the Insert tab of the Ribbon.
Create a Pivot Table by selecting your data and clicking on the PivotTable button on the Insert tab of the Ribbon.

Pivot tables can be a little intimidating for amateurs because there are almost infinite ways to organize the data. However, once you learn and understand how to use them, it will become the first choice for sensibly organizing data.

5. VLOOKUP

VLOOKUP is a wonderful function that allows you to look up information in a spreadsheet or a workbook. It is a useful tool for marketers who deal with a large amount of data. It can be hard to find certain information when dealing with a workbook.

You can collate data from various spreadsheets and workbooks for reporting purposes. Imagine pulling the relevant information from different places to create sensible reports. It significantly reduces the time it takes to pull data and produce reports.

Once you understand this function, you can forget about the days you would go through hundreds of excel files looking for information.

For a quick lesson on VLOOKUP, check out our Youtube tutorial explaining VLOOKUP in 2 minutes.

6. Trendlines

Creating graphs is one thing, but identifying trends from graphs is an awesome tool all marketers can benefit from using. Trend lines will help you to see the trends from the data you have on your spreadsheets. You can identify upcoming trends and follow up to see whether it is a viable marketing avenue.

Add a trend line to your charts by clicking on the + icon next to the chart and selecting Trendline.
Add a trend line to your charts by clicking on the + icon next to the chart and selecting Trendline.

The biggest advantage of trend lines is that Excel can identify the best-fit trend line from the data you have. You can identify many things, including sales, buyer preferences, marketing performance, and even future trends from marketing data.

Wouldn’t it be useful to know when the results of your marketing effort begin to go down? You can change your marketing campaigns before it is too late.

7. Filter

The filter option is excellent when you want to understand the data you have. You can filter data depending on columns and see the entities that meet your filter criteria.

The filter option is fantastic for marketers who have to provide different kinds of reports from their data. You can filter all the male customers in your data and create a chart. You can also segment the data in terms of figures in the columns. Filtering data can help you understand different aspects of your data fast without the use of any formulas.

To filter data, first make sure you have converted the data to a table. To do so, select any cell containing your data. Then, go to the Insert tab on the Ribbon and click on Table. In the popup that opens, select whether your table has headers and click on OK. You now have a table.

Creating a table in Excel.

Once you’ve created the table, you can filter items using the arrow icons next to the headers of the table:

Filtering the values of the table.
Filtering the values of the table.

How Can You Learn Excel?

Most marketers do not think that they need to enroll in Excel classes. However, data analysis can become the secret weapon of your marketing strategies.

Wouldn’t you want to know the next emerging trends? Wouldn’t it be awesome if you could predict the product that clients are going to buy in large quantities? Understanding the best Excel tricks for marketers will help you bring your A-game at work. You will be able to allocate your marketing budget to campaigns that will have high returns.

If you’re interested in getting good at Excel, we can help. A good first step is to sign up for the newsletter. When you do, you’ll also get the Free Excel Cheat Sheet. It ​can boost your productivity and help you take the first step into becoming a confident Excel user.

Read More
Learn Excel quick with bite-sized learning.
Uncategorized

How to Learn Excel Fast: 3 things you need to know

Sometimes you’re just strapped for time. You may be applying for jobs and looking for a quick CV boost. Or you might need to learn Excel fast for an upcoming project that requires it.

Whatever the reason, it can be a relief to hear that it is possible to learn Excel fast and without much hassle.

In this article, we’ll share the three principles you can use to be up-and-running in Excel in no-time. Let’s get started.

Principle 1: The 80/20 rule

We’re not here to beat around the bush: being efficient in your learning may be the most important part of learning Excel quickly.

That’s because Excel has many features that are not needed for regular business use. For example, you will not be checking Workbook Statistics a lot when you use Excel as a regular user. Furthermore, you will not be making a lot of screenshots using Excel Camera. And you will probably never use half of the more than 400 functions that Excel has to offer. Yet if you search for any of these terms online, you will find long explanations of every little thing there is to know about them.

So for an Excel Beginner, it becomes difficult to know which features are important and which ones are not.

What Excel features are important?

To make sure your attention is not diverted to parts of Excel that you will (almost) never use in business applications, you need a learning plan. A plan that contains the most important parts of Excel, without any of the unnecessary features. And that brings us to the 80/20 rule.

You may have heard of this rule before. It’s more formally called the Pareto principle. It basically means that 20% of the actions produce 80% of the results.

The idea is that we apply this 80/20 rule to learning Excel. Because if we can identify the 20% of Excel features that are used in 80% of the business cases, we can learn just those and be super-efficient with our time.

The uneven balance of Excel features. Learn Excel quickly by focusing on the important ones.
The uneven balance of Excel features. Learning the important ones will give you a huge advantage.

The most important Excel features

So which Excel features are the most important? The answer to that question is different for everybody of course. But we’ve made a shortlist of features that we think should be part of every learning plan. Here it is:

  • Formatting and customizing
  • Basic formulas
  • Keyboard shortcuts to minimize mouse usage
  • Lookup and conditional formulas
  • Data visualization using charts & tables
  • Pivot tables

Not a very long list, huh? Learn each of these and you will outperform most of your colleagues. And you’ll be able to get there in a fraction of the time that it has taken them to get where they are.

The 80/20 rule in practice

Copy this list and use it for your own studies if you want to. You’ll be able to speed up your learning a ton.

We use it for our own Excel beginner course as well. So if you’re looking to use this 80/20 principle, but would like some more guidance, you can check the course out here.

Learning only the important parts of Excel will reduce the time that you need to spend on learning. However, it won’t make sure that the things that you do learn will be easy and quick to take in. To do that, you can use a technique that will reduce your time spent learning Excel and improve your motivation to keep going. And that’s exactly what the next principle is all about.

Principle 2: Bite-sized learning

For the past few decades, since the introduction of the personal computer, people have been using the computer more and more to learn new things. They search online for a quick tutorial, or they even learn entirely new skills through online courses. This combination of technology and learning is called e-learning and it has been on the rise for quite a few years now.

And with this rise in the use of e-learning came a rise in the number of people that could attend the same lessons. Before, there was a maximum number of people that could physically attend a class at the same time. Now, people from all over the world can tune in and E-learning classes can grow into the tens of thousands of people.

And because these lessons are all digital, the data that comes from them can be collected and analyzed more easily than ever before. This results in new scientific insights on learning methods and their effectiveness.

Microlearning: learn things fast

One of the areas that has been studied more is called Microlearning. Microlearning is basically about learning in short bursts. So instead of an hour-long lecture, it’s more like a 5-minute lesson.

There are benefits to learning Excel in short bursts.

First of all, you retain more information. A study from the Dresden University of Technology showed that a studying approach with short lessons led to higher performance on a test about the material.

Secondly, there is a higher chance you will keep learning when the lessons are short. A SoftwareAdvice survey showed that 58% of employees would use their company’s learning tools if they made use of shorter lessons.

And finally, and this may be the most important reason of all: short lessons leave you with time left for the other important things in your life.

Because of these three benefits, bite-sized learning is principle #2 for learning Excel fast.

Bite-sized Excel learning leaves more time for baking bite-sized cookies ;)
Bite-sized learning leaves more time for baking bite-sized cookies 😉

Microlearning in practice

How to use this in practice dependends on your learning method. But to give you some guidance, here are the guidelines we use:

Learning sessions of about 5 to 10 minutes per subject. No long lessons​ and no ​boring introductions. Tests and quizzes should be short and there should not be much time in between two tests.

Adhere to this principle and you will be able to learn on your coffee break at work, on the bus to school or any other time when you just have 5 minutes.

The barrier for getting started won’t be as high as for hour-long lessons. This way you will learn Excel faster, simply by learning more often. And you’ll have more fun in the process.

More motivation and quicker learning are great. But there is still one thing missing: remembering. Because although you may learn new things quickly, you won’t benefit if you forget about them quickly as well. So the final step should be to keep them memorized. And for that, there is the third principle.

Principle 3: Get practical

The final principle of learning Excel quickly is to get practical. Why? Honestly, there are a lot of reasons. In our opinion, there is one main reason and many secondary benefits. Let’s start with the main reason.

A hypothetical scenario

To explain this one, we’re going to start with a hypothetical. Suppose you’re going on vacation to a tropical island. The beaches are nice and you’ve decided that you’re going to be taking surfing lessons there. You can choose between 2 types of lessons.

The first type is mostly focused on the theory: how to stand on the board, how to keep your balance, and what to do if you fall off.

The second type of surfing lessons are more about practice: you get to catch your very first waves and enjoy the sport all day long.

Now which of these do you think is going to be more fun?

The theory filled lessons, or the practice filled ones?

It’s an easy choice

We would bet that at least 90% of people would pick the practical lessons. It’s just more fun to actually get to do things yourself.

Of course, the theory is important. It may really help to get you to understand how to make sure you don’t hurt yourself. It may even improve your technique on the board later. However, it simply will not be as fun as riding the waves during the practical lessons.

Learn Excel like you would learn to surf: by doing.
Learn Excel like you would learn to surf: by doing.

Fun is motivation

That’s why we think learning by doing is important: it’s more fun. And if it’s more fun, you’re going to be more motivated and you’ll learn much more. So whether you’re learning to surf or learning Excel, it helps to get practical, if only for the fun you’ll have.

Besides, we know Excel is not an exciting topic for most people. If practical lessons make things more enjoyable then we should definitely use that to our advantage.

More reasons for practice

So that’s the main reason. But there are quite a few other benefits to learning by doing:

  • You’ll get a better understanding of working with Excel.
  • You’ll know if you actually like working with Excel.
  • Any mistakes you make are super valuable for finding where you need to improve.
  • You get to be more creative by solving things in different ways.
  • You will be more prepared for real-life problems.

Plus, afterwards you’re probably going to be more confident in your own Excel skills. Because you’ve proven to yourself that you can do it by solving a ton of exercises.

How to get practical

Looking at all of these benefits may make you realize that you’re missing out if you’re trying to learn Excel without practical elements.

So how do you add these to your learning plan? We’ve made a page specifically for this: Excel exercises. Also, if you’re just getting started with this, you may want to check out our Excel exercises for beginners.

The great thing about these exercises is that you can complete them from within your browser. So you don’t even need to open Excel!

How we use these principles to make you learn Excel fast

For our course, we’ve also taken these principles to heart. That’s why we structured it to use the 80/20 rule. The lessons are short, between 5-10 minutes. And we use exercises in an interactive spreadsheet editor and tests after every module to help you practice.

So if you’re looking to use these principles and would like a more guided experience to learn Excel fast, you may be interested in our Excel Foundation course.

We hope this article was able to show you how to learn Excel fast and that it gave you practical tips to get started. If you have any more tips and tricks for learning Excel as quickly as possible, leave them in the comments. We’d love to hear from you.

Read More
Curious cat
Uncategorized

Excel Beginner Exercises

In this article, I’m going to walk you through a couple of Excel exercises that are perfect for beginners. We will slowly ramp up the difficulty from beginner to advanced so if things are too easy for you, just scroll down a bit.

Getting started with formulas

‘Formulas?!’ You may be thinking. ‘That’s way too advanced for a first Excel beginner exercise.’ It’s not really. Let me explain why: formulas and functions are the bread and butter of Excel. If you want to work with Excel, it is wise to at least know what a formula looks like and why a formula is handy. That’s why we’re starting with this. Don’t worry, they are really not as complicated as they sound.

So let’s jump into it. We will be calculating the result of 32+57. However, we won’t be doing that ourselves, we will be making Excel do the hard work for us.

To start with a formula in Excel, we have to fill in the equals sign = into a cell. After that, we add our formula, so 32+57 in this case. The resulting cell content becomes =32+57.

Spreadsheet editor
Well done! That wasn’t very difficult, was it? But it is a humble beginning to a very powerful feature of Excel. Continue below for more!

Note: all of our exercises have a Show answer button in the top-right that you can click if you cannot figure out what the answer has to be. After clicking Show answer, you can click on the cell to see the formula that was used in the formula bar.
You can also reset the editor back to its default contents using the Reset button.

I hope the number 89 is showing in cell A1 of the spreadsheet editor above. The cool thing about this is that you can substitute the + for a minus – and it will also work. Or you can use the asterisk * to multiply. Or the slash / to divide. You can try all of these different calculations in the editor above if you want.

And just like that, you have learned how Excel formulas work! You can now get rid of your calculator 😉

Using Excel beginner functions: SUM

Now that we’ve seen how to include formulas in Excel spreadsheets, we can get to the real deal: functions. Functions are basically formulas with names. It’s easiest to understand with an example:

The SUM function will sum the values that you give it. We can recreate the formula that we created in the exercise above (=32+57), but using the SUM function. That would look like this:

=SUM(32,57)

If you write this in a cell, it will show 89. Exactly as you may expect. But you can add more numbers (separated by commas) to the function: =SUM(32,57,1) will result in 90, for example.

References

In this next exercise, we will combine the SUM function with another interesting Excel feature: references. So what are references? Once again, an example will explain this very quickly. Look at the following formula:

=SUM(A1,A2)

What do you think the result will be? The answer is: it depends. That’s because the formula uses references to cells A1 and A2.

We know the SUM function will sum the values that you give it, but in this case we give it references to cells. When you do that, it will instead sum the contents of the cells that you reference. So the result will be different depending on the values in cells A1 and A2. If cell A1 contains 1 and cell A2 contains 2, then the result will be 3.

These references can be super handy because they update automatically if the contents of the cells change. Whenever you edit the value in cell A1, the result of =SUM(A1, A2) will also automatically update! And that’s why references are so ridiculously useful.

Now, let’s get to the exercise. We will be using the SUM function to calculate our total monthly income after expenses.

Calculating income using SUM

Spreadsheet editor
Great job! If this was your first function, you can be extra proud of yourself. Continue below for more Excel skills.

I hope that wasn’t too difficult. Or if it was a little difficult, that when you looked at the answer, it made sense.

Note about the answer: if you looked at the answer to the above exercise, you saw a notation that you may not have seen before. =SUM(B2:B5). The colon : is used to mean a cell range. In this case, that means cells B2 up to and including B5. So it is equivalent to using =SUM(B2,B3,B4,B5). In Excel, there are often multiple ways of solving the same problem. I’d encourage you to use the solution that is most intuitive to you.

A step up: Average

Calculating the average of a group of numbers is quite simple: you sum them all up and divide by how many number you have. For example, the average of the number 1, 2, 3, 4 and 5 is: 1 + 2 + 3 + 4 + 5 divided by 5, because there are 5 numbers.

You could do this in Excel by typing =SUM(1,2,3,4,5)/5 into a cell. But there is an easier way. You can simply use the AVERAGE function. Let’s do so in an Excel exercise.

Note: you may have noticed by now that both of these functions are written in all-caps. That’s just the way function names in Excel are. So it may look like I’m screaming SUM at you, but that’s just how it is written.

Spreadsheet editor
Nice! If you’ve completed the SUM exercise, this one shouldn’t be too hard. It’s just a matter of using the right function and filling in the right references (or filling in the numbers directly, if you want). I hope you’re getting the hang of it, these are really some of the most crucial Excel skills you’re learning right now. Keep up the good work.

More practical: Concatenate

We’ve seen functions that use numbers, now let’s look at another type of function: text functions. They are functions that take text as input or that result in text output (or usually both).

Before we get to the exercise, you need to know something about text in Excel formulas. Texts in Excel formulas are almost always surrounded by double quotes “. That’s just the way to let Excel know that it is a text.

Concatenate first and last names exercise

For the fourth of our Excel beginner exercises, we will be combining the contents of two cells. The first cell will have a first name, the second cell will have a last name in it. We would like to fill the third cell with the first name, then a space and then the last name.

We can do this using the CONCATENATE function. The CONCATENATE function looks like this: =CONCATENATE("text1","text2","text3"). If we would run this function, it would result in text1text2text3. So it just combines the texts that you give it.

Spreadsheet editor
Great success! With this exercise, I hope you see that these functions can be used in real life situations. And not just when numbers are involved. Continue below for more exercises!

More Excel Exercises

That was it for our Excel beginner exercises. I hope you’ve learned something new today. If you just can’t get enough of these types of exercises, we have an Excel exercises page filled to the brim with exercises to dig your teeth into.

Read More
Stop sign to tell you to stop using Vlookup
Uncategorized

Why You Should Stop Using VLOOKUP

A lot of us are familiar with this function. You could even make a case that it’s the most (in)famous Excel function out there: Vlookup. The Vlookup function (Vlookup stands for Vertical Lookup) is widely used in business and personal Excel use alike. Whenever a value needs to be found in a long list of entries, a formula containing Vlookup is probably the first thing to come to mind.

It can be complicated for beginning Excel users to wrap their heads around, but once learned, Vlookup can be used as a powerful and versatile tool. It can save you hours of time by looking up values that do not have to be found by hand.

However, there have always been problems with Vlookup. Drawbacks that any advanced Excel user can tell you about. And now that a new function has been introduced that does not suffer from the same problems, Vlookup may not be the function that you want to be using anymore.

What’s wrong with Vlookup?

Let’s make one thing clear: Vlookup is still a useful function. It does what it should do and many workbooks thrive with it. But there are two main problems that you don’t see from the surface that can get in your way.

The first issue is that Vlookup only looks up values from the leftmost column. If you have a table where you want to return an entry based on a value that is not in the leftmost column, you cannot use Vlookup. Alternatively, you can reorganize your table and change the column order to have the column that you need as the leftmost column. But who wants to completely reorganize all their data due to an arbitrary limitation of Excel?

Then there’s the second issue, which is that Vlookup is not capable of looking up values to the left of the lookup value. Here’s an example to illustrate this:

Suppose you have a table containing first names and last names, both in their own column. If you are interested in looking up a first name and showing the corresponding last name, you could use Vlookup. That all works out well.

However, if instead of using the first name to look up the last name, you would want to use the last name to look up the first name, things go wrong. Vlookup is not able to look up a value in a column that’s to the left of the known value. So if the first name column is to the left of the last name column, Vlookup cannot return it.

There are more problems with Vlookup, but we won’t go into those in this post because it would make for a way longer post than necessary. Although if you want to know more about this, you can read all about them in the Drawbacks section of our Vlookup function page.

So what is the solution?

Lightbulb as a metaphor for the solution.

Now that the drawbacks of the Vlookup function have become clear, we can look at some solutions.

The solution that most people use is really more of a workaround. The idea is that you combine the Index and the Match function to each do a part of what Vlookup would normally do: Match can find the value that we’re looking for and return a position and Index can use that position to return a value in a different column. This works and it makes you appreciate how you can solve problems in many ways in Excel. We’ve even created a video explaining this solution.

The thing with this solution is that it makes things more complicated. You’re basically combining two functions to get the same functionality as the one you are replacing. It would be more intuitive to have a single function that can just do a vertical lookup, but also work with values to the left.

The great thing is that this function now exists. In September 2019, Microsoft announced the XLOOKUP function: “The successor to the iconic VLOOKUP function.” This function can do what Vlookup can, but better. And it doesn’t just replace Vlookup, it also replaces its lesser known brother, Hlookup.

Xlookup: the new hotness

Let’s take a look at the Syntax of Xlookup:

XLOOKUP(Lookup value, Lookup array, Return array)

Where Lookup value is the value we are looking for. For example, a last name. Lookup array is the array or cell range that contains this value. So that would be the column containing all last names. And finally, Return array is the array or cell range that contains the value we want to return. Which would be the column containing all first names in this example.

By being able to hand-pick what array the lookup takes place in while also being able to choose the array where a value should be returned from, it all becomes much more flexible. Not only does this allow us to look up values to the left, but it also makes for a function that’s more intuitive than Vlookup ever was.

That’s in part due to it using just three arguments. It’s really that easy. Although you can make it more complicated with the optional arguments:

XLOOKUP(Lookup value, Lookup array, Return array, [If not found], [Match mode], [Search mode])

The If not found argument allows you to choose a value that should be returned if no match can be found. So that means less tedious #N/A errors.

Match mode allows you to change the way the function matches: 0 (the default) for an exact match, 1 or -1 to match against the nearest smaller or larger item (if there is no exact match), and 2 for a wildcard match. You can learn more about wildcards from the Wildcards section of this post.

Finally, Search mode lets you decide the direction of the search, 1 for first-to-last and -1 for last-to-first. You can also use 2 and -2 for binary searches. If you don’t know what that means, don’t worry, it’s only meant for expert users.

Availability

Really a lot of issues have been solved with this all-new function. But there is one catch: as of writing this, the Xlookup function is not available for everyone yet.

People subscribed to Microsoft 365 (Monthly channel) will have access to it but others still need to wait. It will be available for Semi-Annual Microsoft 365 subscribers from July 2020.

If you’re not using Microsoft 365, but instead are on Excel 2019, Excel 2016, Excel 2013, etc. you will sadly not have access to Xlookup. This may be a good time for you to consider upgrading 😉

We hope you’ve learned something new today. Let us know in the comments how you feel about Vlookup and Xlookup and if you plan on upgrading your workbooks.

Read More
Old man
Uncategorized

Calculate Age in Excel – You might be doing it wrong

In this short video, I'll tell you how to calculate ​someone's age based on a date of birth and how you may be doing it wrong right now.

​Once you realize that ​age is just the difference in years between a date of birth and the current date, it becomes easy to do. Check the video for the complete explanation.

V​ideo

​Formula

The formula to calculate age ends up being:

​=DATEDIF(Date of birth, TODAY(), "y")

​Pretty simple, huh?

​How many days old are you?

You can use other arguments instead of "y" to also calculate how many months and days someone is old. For example:

=DATEDIF(4/29/2020, TODAY(), "ym")

will return the number of months since this person's last birthday. So if your birthday is ​​January 1st and it is currently ​February 1st, this will return 1.

You can combine this to calculate age in years + months. For example, if the date of birth is in cell A1:

="You are " & DATEDIF(A​1, ​TODAY(), "y") & " years and " & DATEDIF(A1, ​TODAY(), "ym") & " months old."

This will return something like: You are 28 years and 3 months old.

You can even add the number of days in there, using "md" as third argument! We'll leave that up to you 😉

We hope you learned something today. It's really quite easy to calculate always up-to-date ages in Excel. ​​If there are any other subjects you'd like us to cover, let us know in the comments.

Read More
Learn how to use Excel without the mouse
Uncategorized

Mouse free Excel navigation: Become 5x faster with Excel

To go from a beginner or intermediate Excel level and become a master, you will need to improve your speed. One of the quickest ways to greatly improve your Excel speed is by learning some keyboard navigation shortcuts. In this video I'll show you what buttons and keyboard shortcuts you need to know to navigate Excel without a mouse.

Get faster and more efficient at Excel by following along. Whether you're on Windows or on Mac, these shortcuts will help you accomplish your Excel tasks faster than before. ​

V​ideo

Read More
Another type of date ;)
Uncategorized

What You Should Know About Dates in Excel

What’s up with dates and times in Excel? What is a serial number? And what does the date January 1st, 1900 have to do with it? Today, we’re de-mystifying dates in Excel. Let’s do this.

Dates in Excel: Serial numbers

In the video below, you can see what happens when you change the number format of a date to General.

What happens when you change a date to a General number format. You can see the underlying serial number that Excel uses behind the scenes.

Weird, isn’t it? What you see here is the underlying number that Excel uses to represent the date. This number is called a serial number. But what does it mean? And why does 4/10/2020 become 43931?

Serial numbers explained

Let us explain: 43931 is the number of days that have passed since 1/1/1900 to the date 4/10/2020. That’s how Excel saves dates.

Knowing this helps us understand some of Excel’s behavior. For example, we can predict what will happen when you calculate 4/10/2020 + 1 in Excel. The result will be 43931 + 1 = 43932. And formatted as a date, you get 4/11/2020, one day later. In the same vein, 4/10/2020 - 7 will result in a serial number that is 7 days before 4/10/2020.

So now you can use the TODAY function to calculate yesterday’s date! It’s as simple as:

=TODAY() - 1

Note on dates before 1900 – You might be wondering what happens with dates before 1/1/1900. You can try this for yourself: in a cell in Excel, type 1/1/1900. You will see the number format change to Date automatically. If you then type 12/31/1899 (just one day earlier) into another cell, the number format won’t change. Excel does not recognize it as a date because it doesn’t have a serial number to use for it. The conclusion is that Excel just isn’t very useful for working with dates before the year 1900.

This is not too difficult, right? It’s just Excel’s way of representing dates. However, it becomes more complicated when we add time as well.

Times

For dates with times, a fraction is added to the serial number to represent the time of the day. For example, 43931.00 is the serial number for the date-time combination of 4/10/2020 12:00 AM.

The fraction is the percentage of the day that has passed at that time. So at 12 AM it’s .0000 and at 12PM it’s .5000. 1 AM is at the fraction 1/24 (.041667).

Overview

Here’s an overview for some different times of the day and their corresponding fractions and decimals.

TimeFractionDecimal
12 AM0.00000
1 AM1/24.04167
2 AM2/24.08333
12 PM12/24.50000
12:01 AM1/24/60.000694
12:01:01 AM1/24/60/60.000011574

You can use these fractions either on their own to only represent a time (0.5 for 12 PM) or combined with a whole number (43931.5 for 10/4/2020 12 PM) to represent both date and time.

So by combining the whole number for the date and a fraction for the time, we can represent any date-time combination from 1900 up until now. Pretty cool, huh?

Getting the current date and current time

Excel has functions that return the current date and time so you can use them in your worksheets. This can be handy for calculating ages based on date of birth. Or calculating how many days are left before a deadline.

For the current date, you can use the TODAY function. We have a page about the Today function here.

For the current date and time, there is the NOW function. We have a page about Now as well, with examples and explanations.

Concluding

We hope you understand the inner workings of Excel a little better after reading this. If you have any more questions about date-shenanigans in Excel, let us know in the comments.

Have a great day!

Read More