Join cells in Excel and remove excess commas between elements

EDIT: If you are using Excel 2019 or Office 365 you can use the TEXTJOIN function to solve this problem. Click here for the new article:

How to Join Text from Several Cells in Excel using TEXTJOIN

Unclean data can cause a lot of problems in Excel. In this post I will show how you can join data from different columns with a comma between them. That’s the easy part. The problem occurs when you have empty cells in your data, like in the table below. The result of the first row looks fine, but if you look at the rows with empty cells, you get too many commas:

EasyExcel_41_1_Join with concatenate and trim



READ MORE

Today’s Shortcut: Zoom in and Zoom out in Excel

This is a standard shortcut that works in most Windows applications, including all MS Office applications and all web browsers:

Hold down the Ctrl key and use the scroll wheel of your mouse to zoom in and out.

Of course, you can also use the zoom bar on the right bottom of the page, but when you know this shortcut you will probably never do that again!

EasyExcel_40_Zoom in and our in Excel

More shortcuts in Excel:



Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

How to Calculate Grades in Excel

How can we calculate the grades (for example A-F) in Excel if we have the test results as numbers? In the example below, a score of 90% or higher is an A, 80-89% is a B, 70-79% is a C, 65-69% is a D and less than 65% is an F.

The first thing we should do is to organize this information in a lookup table:

EasyExcel_39_1_Calculate grades in Excel



READ MORE

Highlight Weekends in Excel with Conditional Formatting

EasyExcel_37_1_Highlight weekends with conditional formatting

The picture to the right shows a table with some sales figures for July. There’s nothing wrong with the table as it is, but I find it very hard to read and make sense of it. There are just a lot of numbers and dates, and you can’t even distinguish between weekdays and weekends. If we could highlight the weekends (or weekdays) it would be a lot easier to read these numbers. In this example I want to highlight the Saturdays and Sundays. Here’s how we’ll do it:

READ MORE

How VLOOKUP can get you in trouble and how to solve it

An Extra Column Means Trouble

If you want to find a value in a table in Excel, a simple VLOOKUP function is usually a good and easy way to do it. But you have to be careful – if you insert a new column in your table, the function might not work anymore, and we have to find another approach. Here’s why:

READ MORE