formatting Archives

Understand The Power Of Concatenation Of Cell Formulas

August 12, 2022 by Barbara

When you are working with data in Excel, you may often find yourself needing to combine the results of two or more separate cell formulas into a single cell. This process is called concatenation. In this tutorial, we will show you how to concatenate cell formulas in Excel and explain the power that concatenation can give you when working with data. We will also provide some tips on how to avoid common errors when using concatenation. Let’s get started!

Back To Basics Concatenation.

The syntax of this formula is

=CONCATENATE(TEXT1,TEXT2…)

Up to 255 text entries can be added to the function and each one of them should be separated by a comma. One point to note, CONCATENATE does NOT add in extra spaces between text, in order to do this, you need to accommodate these extra spaces within the formula.

Also note, you can also use CONCAT, TEXTJOIN or & in the same way. Use whichever you feel comfortable with OR what is available in your version of Excel.

So, don’t be put off by this CONCATENATE terminology. The concatenation of cells is one handy little Excel feature that is fabulous. Basically, it means combining the values of two cells. The great thing about Excel is that it has functions that work with arithmetic functions but it also ones that work well with text and Concatenation is one of them.

Concatenation Excel Example.

Heres an example of when you might find concatenate in Excel useful.

Suppose you have a first name in one cell and last name in another column, and you need to combine then in one cell for a mail merge or some other administration exercise. In this example I want to generate a username with the first and surname. The sample data set I am using is below.

There are actually a few ways to concatenate the information

Use the built-in formula in the new cell where you want the new information

=Concatenate and follow instructions on screen entering the first text, then the second. Hit return and voila your results are displayed.

So, to generate the username in this example, the formula is below.

=CONCAT(B3,” “,C3)

Notice the extra space added. Any extra punctuation including commas and spaces etc need to be manually added.

Alternatively there is another way way which some people find a lot easier. This is using the & symbol. Let’s suppose you have the same scenario where you want to combine first and last names in to one column. Typing the following will achive this Same result. Possiby a bit quicker.? What do you think?.

Excel is a powerful tool for organizing and analyzing data. With the concatenation feature in Excel, you can quickly and easily merge cells into one long string. This can be useful for creating labels or headers in your spreadsheet, or for combining data from multiple sources into one cell. The example used was to join first name and lastname in our data source with little effort. To concatenate cells in Excel, select the cells you want to merge and use the CONCATENATE function. We’ve shown you how to do this using both the standard formula and the quicker way using the & symbol. Feel free to browse our other Excel tips, videos, Ebooks and sign up to the Excel At excel Newsletter for three free tips every month.

More Excel Tips

Why Is Excel Changing The Last Digit Of My Number To Zero?

September 22, 2021 by Barbara

Hello Excellers, welcome to another blog Excel post in my #ExcelTips 2021 series. Today let’s look at why Excel Is changing the last digit of a number to zero. That’s correct Excel changes the last digit to zero sometimes. If you have ever come across this, it can be confusing and damn frustrating. You type a long number into Excel, and weirdly anything past digit 15 appears as zeros. So if you are entering a credit card number, which is usually 16 digits, then the last is displayed as a zero.

Because Excel only stores 15 significant digits in a number, Excel changes any remaining digits to zeros. As the formatting of the cell is numbers, Excel interprets the numbers as being intended for calculation. Okay, so can we reformat them as Text or something else? Well, no, once entered, you cannot just reformat. Instead, you will need to format new cells as Text then enter the numbers again.

The Solution To Solve Why Excel Is Changing The Last Digit To Zero.

Hmmm, so is there anything else we can do to avoid having to re enter all of those numbers?…there are a few things.

Solution 1. Leading Apostrophe.

The first solution is to insert a leading ‘in front of you number. So, for example ‘2560089996999987 would display correctly. You can see in the screen grab below without the apostrphoe Excel changes the digit to zero.

Solution 2. Using The Text Import Wizard.

Now, onto the second solution. If you are importing your data from a .rtf or.csv file, when the text import wizard appears, scroll to your field containing the offending ‘longer than 15 digits’ number. Click on it, then in the upper right hand section of the window, change the field of column type from ‘General’ to ‘Text’.

text import wizard to prevent excel changing last digit to zero

Note. The Text Import Wizard is a legacy function. If you are using esrlier version of Execl then you will be ok. If you have a newer version of Excel you will see the Get & Transform Tools. So since In Excel 365 and 2016 the “Text Import Wizard" has been removed. But, if you want to use the legacy version of Text Import Wizard you can get it back.

Restore The Text Import Wizard.

Follow the steps below to restore the original text Imprt Wizard.

FIle | Options | Data
Select Show Legacy Data Import Wizards
Hit Ok

You can now go ahead and use the Legacy Data Import feature to comportably import your data the way you want to.

why is Excel changing last digit to zero

Next select your file to import. After selection hit Import.

I always make sure to check out any data I am importing to see if this issue may occur. It is easier to catch the issue at import stage than having to either get a leading apostrophe or re type. Who wants that right?.

More Excel Tips.

Proper Excel Formula

August 28, 2020 by Barbara

Hi Excellers and welcome to another #Excel #FormulaFriday blog post in my 2020 series. Today let’s look at how useful the PROPER Excel function. This little formula does what it says. It converts your text to it’s ‘proper’ case. Essentially it will turn the first letter of each string to uppercase and following letters to lowercase.

PROPER Function Syntax.

=Proper(cell reference)

OR

=Proper(“your text”)

Let’s work through an example. See my sample data in the screen-shot below, “sales person” is converted to “Sales Person” by using the cell reference- A1, or you can convert your text string as long as it’s contained in ” “.

Using The Cell Reference With PROPER.

Below you can see using the cell reference.

Using The Text String WITH PROPER.

Or you can use the text string enclosing your text in ” ” as in the screen shot below.

This little formula can be useful to fix any case issues in Excel worksheets really quickly, in particular using the cell reference method (which I must admit I use the most). Have you used this Excel function before?

Need A Macro Solution To Convert Text Into It’s Proper Case?

Macro Mondays – Convert A Range OF Cells To ‘Proper’ Case With An Excel Macro

Want To Watch The Video?.

If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below

How To Excel At Excel – Formula Friday

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

Combine Conditional Formatting With An Excel Formula – Identifying Duplicate Values

November 9, 2018 by Barbara

Hello Excellers, welcome back to another blog in the #formulafriday series. Today let’s look at how to combine conditional formatting with an Excel formula. What are we going to achieve with that?. Well, we can easily identify duplicate values using the combination of these features. So let’s take the problem that John has. He has two lists of guests to a retirement party. Another colleague of his has also been collating a list and John needs to identify duplicate names quickly and get one master list used for the party. He has the two lists List 1 and List 2.

So, the method John wants to use is to highlight visually any duplicate names on the two lists he and his colleague have. Of course with as little effort as possible. The results he wants to see are in the screenshot below.

Conditional Formatting Basics.

Excel comes with a number of preset conditions that you can use to conditionally format your Excel cells. You can see the options some super quick options available by selecting the following options

Home Tab| Conditional Formatting

If you want to get a bit more flexible then select the following options

Home Tab | Conditional Formatting | Create New Rule.

The last option in the New Formatting Rule dialogue box is where we can get a bit more creative. Use your own custom formulas to create any number of conditional formatting rules. The Conditional Formatting must return a TRUE or FALSE or numerical equivalent. An example of some formulas that work perfectly with this type of formatting.

=A2>50

=ISNUMBER

=ISODD

When the formatting is applied to a range of cells, the key is to enter the formula cell references that begin with the first row and column (or the top left cell) in the range. The formula is then applied to each cell in the range respectively. Just think of how you normally create formulas and then copy the formula to the rest of the cells you want to. So, back to John’s issue today, how to write a conditional formatting formula to identify duplicated name entries in hi party lists

Let’s Write Our Conditional Formatting Formula

John’s Solution is s simple one. We can use a formula to highlight any cells that contain duplicate data which returns TRUE if a cell value appears more than once.

Select Home Tab | Conditional Formatting | Create New Rule
In the Edit rule Description | Format where this formula value is true, type the following formula

=COUNTIF($B$3:$D$14,B3)>1

Then select your formatting is the result if TRUE
Hit Ok

It is important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B3 in this case.

How This Formula Works.

COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B3 changing and $B$3:$D$14 remaining unchanged.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.

Change Colour Of Worksheet Tabs

November 6, 2018 by Barbara

Hello, Excellers. Today’s blog post is a coffee break quick Excel tip for you. So, I was recently asked how to make Excel worksheet tabs stand out by changing their tab colour. In particular, this user wanted to colour code their regional sales areas with different colour worksheet tabs. You can see the requested result in the screenshot below.

First of all to note is that the ability to change the colour of worksheet tabs was only introduced in Excel 2002 (Office XP). So if you happen to be using a really old version of Excel you will be disappointed. Changing the colour of the tabs is a really simple process.

Change The Colour Of Worksheet Tabs

The tab colour can be changed by either using the Menu Method

Click worksheet tab you want to change
Home Tab
Format Group
In the Organise sheets section select Tab Colour
Finally, select the colour of choice

Alternatively, you can use another way to achieve the same result using a Shortcut Method

Select the worksheet that you want to change
Right click with your mouse
A shortcut menu will pop up
Select the colour of choice as with the method above

Note: Only inactive worksheets will display the colour chosen.