Highlighting trailing spaces with Data Validation

In part 2 of our trilogy about trailing spaces (part 1 is just here) we look at how to identify trailing spaces if you have them in your data sets. As discussed in part 1, they're hard to spot, as we can't see spaces with the naked eye at the end of a cell. In this post, we'll look at how to identify them using Conditional Formatting.

First, I have some "raw" data, which contains some trailing spaces which I can't currently see:

Now, click on Conditional Formatting on the Home tab:

 

In the drop-down, choose Highlight Cells Rules then More Rules:

Then, in the Conditional Formatting dialogue, choose the second option: Format only cells that contain then specific text, ending with and then insert a space in the text box:

Then click the Format button and format these highlighted cells as you like. For me, the finished data then looks like this:

If you'd like to have a go at this yourself, you can download an Excel file with the completed example, and a raw data set for you to try yourself. Click here to download the file.

Preserving cell formatting when filling down

In this blog post, we're going to take a look at Excel's Fill Down function, and what that means for formatting.

The Fill Down function is one of the greatest time-savers in Excel. Create a formula with relative references, drag the formula down and watch your data complete itself automatically! It's a great tool, until formatting gets in the way and spoils the fun.

For those unfamiliar with the tool, the idea is that you select the cell with the formula to copy, click on the little black square in the bottom-right corner, and drag it down to create further formulas automatically. You can try it in the attached example.

A frustrating problem is that doing this also copies formatting, so a nicely-formatted table as in the attached loses its colours and you need to spend time re-formatting.

The solution is to use the right mouse button instead of the left. As you let go after dragging, you'll be presented with a number of options. The one you'll need is "fill without formatting" - the formula or data will be copied as before, but formatting will remain in place.

We have an online video and downloadable example file to demonstrate this e-tip - watch the video just here.

Using Data Validation to prevent users entering trailing spaces

A question came up from an attendee on a recent Working With Data In Excel live e-learning course, which is a common request. Today's blog post will explain how to prevent users from inserting preceding or trailing spaces into a cell.

Why is this a problem
Imagine you have a column of data, containing the values "Red", "Green" and "Blue", which a user has entered manually. Except the user has entered some extra spaces after some of the values so that your column of data (column A) looks like this:

   Red
   Green
   Green
   Blue
   Green[space]
   Blue
   Red[space]
   Red
   Green

I then create a formula to count the number of cells containing the value "Green", like so:

   =COUNTIF(A:A,"Green")

How many will there be? It will tell me there are 2. Why? Because "Green[space]" is not the same as "Green" in Excel.

So as you can see, it becomes important to prevent users adding extra spaces after their data.

How do we stop them?

We can prevent leading or trailing spaces using Custom Data Validation. Here are the steps to work through...

1. Select the column you want to protect.
2. Click on "Data Validation" on the Data tab.
   
3. In the dialogue that pops up, choose "Custom".
   
4. Enter the following formula into the box, replacing the references to "A1" with the first cell in your range: =TRIM(A1)=A1

Then play with it!

A caution...
Unfortunately, Data Validation in Excel doesn't prevent people pasting incorrect values into the cell - only typing manually. There are 2 work-arounds for this, which we'll look at in future blog posts:

1. Prevent people pasting anything into the sheet using a Macro, or
2. Set up Conditional Formatting to show if cells have trailing spaces which have been pasted in.

Keep an eye on the blog for these other 2 parts, or why not sign up for our free e-tips on our website and get them straight to your inbox?

Get our Excel tips direct to your inbox and make sure you never miss a top tip - fill in your details just here.

Using greater than and less than in conditional formulas

By Neil Shorney, Director, Naturally Sales Ltd.

Here is a problem using SUMIF and COUNTIF with a slightly counter-intuitive example, yet a problem which can occur quite frequently. We've previously looked at how we can count IF a variable is equal to a particular value. It gets a little more tricky if you want to count (or sum) when a variable is greater than a particular amount.

The problem is that > is considered a "special character" and can't be used as-is in formulas. We get around this problem by enclosing it in speech marks as follows:

       =COUNTIF(A:A,">8")

It may look a little odd, but it works and is a very useful formula.

Coming up: Microsoft Excel level 3 live e-learning

Don't miss our next online Excel course running Friday next week, 29th April, at 2-4pm UK or 9-11am Eastern Time. You'll learn about such essential data tools as Pivot Tables, Data Validation and Macros, all in 2 hours from the comfort of your desk! Full details here or book just here.

Some changes in Excel 2016

One of my tasks I set myself for the beginning of 2016 was to have a proper look around Excel 2016 and get to understand the changes which came with it, along with deciding whether or not I should advise our clients to upgrade. So here are my thoughts after having really played with this new Excel version…

Reassuringly familiar
If you're currently using anything from Excel 2007 onwards, then the good news is that there's nothing you have to understand in order to get started on Excel 2016. There have been a few minor tweaks in the location of certain commands on the ribbons, but nothing you won't find pretty quickly. I briefly lost the Trendline, which has been moved from the “Layout” contextual menu to a right-click menu directly on the data series on the chart.

I also found that the behaviour when grouping dates in Pivot Tables was very slightly different – some new boxes were ticked by default. Again, not a big deal, if slightly annoying.

Very pretty
Of course, this shouldn't be a consideration when considering a software upgrade, but the fact is that I just like Excel 2016. It's elegant, smooth and modern. It has nice animations when moving from one cell to another – rather than jumping straight there as in Excel 2010, the selection glides to the new cell very gracefully. The colours are nice. I just like it! Take a look at one of our videos to see it in action.

New and improved features
The main consideration should be whether the new features justify an upgrade. There are some improvements, as well as some new features which make this an attractive piece of software.

Improved autocomplete
In previous versions of Excel, the autocomplete in the formula bar brought up functions beginning with the letters typed. The improved version brings up any functions containing those letters. For example, if you've forgotten the function SUMPRODUCT but remembered it contained the word PRODUCT, you could begin typing and the SUMPRODUCT would be listed.

New functions
There are some great new functions available in Excel 2016. My favourite is the IFS function, which simplifies the use of nested IF statements to report on multiple criteria. Others include MAXIFS and MINIFS, which work the same way as the SUMIFS function from Excel 2007.

Recommended charts
One problem we often encounter when teaching or consulting in Excel is that users rarely understand which chart type is best for their data set. It's driven more by the data than by the look of the chart. The Recommended charts button suggests those charts best suited to a particular data type.

Other new features
There are other new features which are well-publicised, but in my opinion less useful, such as Excel 2016's ability to directly import data from a web page, such as Wikipedia. I can see that in theory this is useful, but in practice, it takes so little time to copy and paste from a web page, that the effort of importing automatically seems a little unnecessary.

“Cool” new language
Finally, it seems that Microsoft is trying to get “in with the kids”. Compare the language between the “save changes” dialogue in Excel 2010 and Excel 2016. It's a little more modern, I guess, but did make me chuckle that the boffins at Microsoft are now trying to be cool.

Excel 2010 

Excel 2016

So should you upgrade? Well, as with most things, it depends! If your current version is 2010 or 2013 and works well for you, then it's not urgent, although some of these new formulas could be useful. If you're on 2007 then please upgrade, because you're using a dreadful, buggy piece of software! Consider that sending a spreadsheet containing new functions to someone using an older version will cause problems, though.

As for me, I have upgraded to Excel 2016, but kept a copy of Excel 2010 for teaching purposes as it's still the most widely used Excel version and is a very robust, high-quality piece of software.

By Neil Shorney, Director, Naturally Sales Ltd.

Turning off an Excel 2010, 2013, 2016 annoyance!

Here is a great Excel tip, because it addresses one of the most irritating features of Excel 2010, 2013 and 2016 - the "Enable editing" button. For a long time this feature drove me up the wall - I would repeatedly open spreadsheets emailed to me and start typing, only to find that I had to click the "enable editing" button before I could do anything. Then I discovered that this can be turned off, and here's how...

1. Open Excel and click on the File button, then select Options 
2. Select Trust Center in the left pane and Trust Center Settings in the right pane
3. Click on Protected View in the left pane
4. Choose your options 

NOTE: However irritating it might be, the button is there for a reason, so don't disable it unless you're sure you won't be opening suspicious files. Naturally Sales Ltd accepts no responsibility for the consequences of following this e-tip. I bet that's the best Excel tip you've been given today - why not share it with a friend by clicking one of the "share" buttons below?

You can sign up to get our Excel tips by email at www.nsales.co.uk/e-tips

How to join multiple Excel cells together in one cell

Here's an email I received from a client the other day regarding a data problem she had:

"Hi Neil, I was wondering if you could help me with something. I have a column with over 1000 email addresses and I would like to join them in one cell, separated by a comma. Do you know how I can do this? The concatenate function seems to let me only add each cell at a time?"

Let's look at how we can solve this problem. It isn't really possible in Excel, but if we copy it into Word, we can achieve this quite easily as follows:

- Copy your rows into Word - they will appear in Word as a table (although the borders may not be visible)

- In Word, on the contextual toolbar "Table Tools", choose the "Layout" tab (see picture below) and "Convert to text" (on the far right-hand side)

- Choose to separate the text with paragraph marks (commas will not work at this stage)

You then have 1,000 rows of text separated by invisible paragraph marks. We now need to replace these with commas (and a space if required)

- Press CTRL+H (find & replace) and replace ^p with , (a comma, or a comma followed by a space if required – if you can’t find the ^ character, just copy if from this blog post)

There may be a few stray commas at the end which you'll need to delete.

This works in Word 2010 - if you have a different version of word there may be small differences. If it doesn't work for you, please comment on this post and I'll get in touch to help you out.

For more help with Excel data, check out our live e-learning course: Excel level 4 - Advanced Data

By Neil Shorney, Director, Naturally Sales Ltd.

Excel tip: combining a large number of values into one cell: http://t.co/zGryBGQurS

— Naturally Sales Ltd (@nsales_co_uk) October 15, 2015