Skip to main content
Tag

Functions

Dec 09

How to join text & values in Microsoft Excel

By Blog, Video

Today I am going to show you a cool trick to use Concatenate to join text and values in Microsoft Excel. To start, I have this simple list of first name, last name, and age. The goal is to be able to paste this into an email, into a word document, or something similar. Instead of just showing the raw data, it would look more like “John Smith is 33 years old.” The excel function that would do this for you is called Concatenate.

In order to join text and numbers, you would use the =CONCATENATE() function. Inside the parenthesis, the referenced cells can contain formulas or other functions. You can add commas or quotation marks to add text or spaces.

For example, the Excel cells we are using are A1 containing the first name, B1 containing the last name, and C1 containing the age. If we input this into the Concatenate() function it would look like this: =CONCATENATE(A1, “ “, B1, “ is ”, C1, “ years old.”). Make sure to account for any spacing that would need to go in between text or values. As you can see between cells A1 and B1 I added a space using quotation marks, which would create a space between the first and last name.

Then you can paste this into the function bar in Excel, and you can see how Excel will select the three cells referenced. Once you hit return, Excel will automatically create the sentence for you. In our example, it will look like “John Smith is 33 years old.”

If you have a list, all you need to do is use auto fill and drag the function down. This will automatically fill out the rest of your list.

You can utilize this in all kinds of situations, whether you want to make something that’s easier to read, or maybe you want to join some values. For more information, you can check out my YouTube video that shows how to use Concatenate.

If this helped you out, please take a moment to browse my website and check out my YouTube channel @In30Minutes for other helpful tips and tricks. For more information on how to get the most out of excel, check out my book Excel Basics: in 30 Minutes, which covers other basic functions of Excel. As well as the Excel cheat sheets and intermediate level cheat sheet which specifically covers Concatenates.

Jun 07

How to sort in Excel: The five-minute tutorial

By Blog, Video

This short, five-minute tutorial will show you how to sort in Excel. It uses a simple, three-column spreadsheet file that shows the annual bonus amounts for employees in various departments.

The concept is covered in “Excel Basics In 30 Minutes”, but it’s also useful to see it being done on the screen, as the video below shows.

Sorting is a very useful way to alphabetize lists, rank lists from highest to lowest (or vice-versa), or sort by multiple criteria (in the video below, department is the key criteria, followed by amount of the bonus).

However, sorting can be a tricky thing, too. A common mistake is failing to “expand selection”, which means only one column will be selected for sorting. The result? The data will be mixed up because the other columns weren’t sorted at the same time. This type of mistake has led to people being sent the wrong mail, as well as financial errors. Don’t let it happen to you!

For more information about sorting and other basic Excel functions, read “Excel Basics In 30 Minutes”. It’s inexpensive — less than the cost of a pizza! Go to this page to see reviews, and this page to place your order.

Video tutorial: Sorting Please “Like” and share after you watch it!