Search Not Just Numbers

Tuesday 27 May 2014

Excel Tip: Remove all formatting

Just a really simple tip this week, as the week started with a public holiday, both here and in the US. That means that you're either lucky enough to have the week off and not interested in spreadsheets at all, or, like me, you've got four days to fit in a week's work!

Have you ever inherited (I'm sure you wouldn't have created one yourself) one of those Excel files with wild formatting all over it? 5 or 6 different fonts in as many different sizes and number formats, and twice as many colours?

Here's a simple tip to strip it all away leaving you with the important stuff - i.e. the data in the cells.

First of all, highlight the range you want to clear (click in grey box in the top left corner of the screen, to the left of the A of the column headings and above the 1 of the row headings, if you want to select the whole sheet).

On the Home Ribbon, over to the right hand side of the screen (below AutoSum and Fill), there is a button called Clear (with a picture of an eraser next to it).

Click this and you will be presented with the following options:

  • Clear All
  • Clear Formats
  • Clear Contents
  • Clear Comments
  • Clear Hyperlinks
Click "Clear Formats" and this will remove all formatting from the selected cells.

That's it. Nice and clean so that you can start again!

Excel Expert Course


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 20 May 2014

Excel Tip: Adding the same cell across multiple sheets

Have you ever needed to sum the same cell across multiple sheets?

You might have 12 monthly sheets and an annual summary or 26 department sheets and a consolidation.

I don't normally design spreadsheets that require this as it tends to go against my advice on how to structure your data.

I would generally advise one data input sheet that covers all months (or all departments) and a report that will present the information for any particular month (or department).

However, I see many spreadsheets that are structured like this and there are times when it is appropriate. In this post, I will show you a quick way to do it.

A reader asked me about this last week as she thought I had written about it in the past, and I had to look it up (as I said, I don't normally have a need for it).

Before I looked it up, I would enter a formula such as:

=Worksheet1!A1+Worksheet2!A1+Worksheet3!A1+Worksheet4!A1+Worksheet5!A1

...to add cell A1 from 5 different worksheets.

This could get very tiresome, of you had, say, 100 worksheets!

The much more efficient way to do it is:

=SUM('Worksheet1:Worksheet5'!A1)

Note that it is the position of the worksheets that matters, not their name. The formula will sum all worksheets between Worksheet1 and Worksheet5 inclusive - left to right.

The reader, when I showed her this, remembered this useful tip from wherever she had read it. Use 'bookend' worksheets if the worksheets to add may change, e.g. have worksheets named First and Last, and place any sheets to be included in the sum in between them.

My thanks to the post that helped me to answer the question:

http://www.ozgrid.com/forum/showthread.php?t=73877

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 13 May 2014

Excel Tip - Adding a conditional Thumbs Up or Thumbs Down

This week, I want to show you a neat little trick for doing something a little more visually interesting than just using brackets to distinguish between positive and negative variances.

In this post, I will show you how to automatically show a green thumbs up or a red thumbs down next to the variance (depending on whether it is good or bad).

Once you know how to do this you can replace these with arrows or some other symbols and make the colours whatever you want them to be.

OK. Say we have an actual figure in cell A1 and a comparison in cell B1. A1 might be this month's sales, for example, and B1 might be budgeted sales, or last month's sales. Either way, we want to make it obvious at a glance that A1 being higher than B1 is a good thing, and A1 being less than B1 is a bad thing.

A green thumbs up, or a red thumbs down, in cell C1, should make this pretty clear - so how do we do it?

First of all, where do we get the Thumbs Up and Thumbs Down symbols from?

We can discover what symbols are available in different fonts by selecting Symbol from the Insert ribbon (this is on the far right of the standard Insert ribbon).

If we select the Wingdings font from the drop-down at the top, you can see there is a Thumbs Up symbol there. Click on it and note the number at the bottom left after the name of the font. In this case it says "Wingdings: 67", so the number to note is 67. This is the character code of this symbol. Click on the Thumbs Down and you will see that this has a character code of 68.


OK, now cancel this screen, as we are going to insert these symbols in a different way - now we know what we want.

We can refer to any character by its character code using the CHAR function. So, if in any particular cell, we change the font to Wingdings (either by right-clicking and using the format cells dialog box, or by selecting the Wingdings font from the drop-down on the Home ribbon),

=CHAR(67)

will return the Thumbs Up symbol.

For our purposes, set the font in cell C1 to Wingdings and type the following formula into cell C1,

=IF(A1>=B1,CHAR(67),CHAR(68))

This formula will return the Thumbs Up character if A1 is greater than or equal to B1, and the Thumbs Down character, if it is not.

If you need a refresher on the IF function, take a look at this earlier post.

All we need to do now is change its colour. We can do this using Conditional Formatting.

First of all, change the font colour in cell C1 to green (again, either from th Home ribbon or Format Cells).

We then want this to change to red if C1 contains a Thumbs Down.

Click on cell C1 and from the Home ribbon, click Conditional Formatting, New Rule, Use a formula to determine which cells to format (this step is covered in more detail in this earlier post).

In the formula box, enter:

=C1=CHAR(68)

and click on the Format button to change the font colour to red.

That's it. Change the values in A1 an B1 and you will see C1 change from a green Thumbs Up to a red Thumbs Down, depending on whether A1 is greater or less than B1!

As I said earlier in the post, you could use other symbols, such as up and down arrows and other colours, depending on your requirements. Have fun with it!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 6 May 2014

Excel - How up-to-date are we?

Before we get into today's post, a quick reminder that Mynda Treacy's Excel Dashboards Course is closing on Thursday (8th May), so if you haven't enrolled yet, you'll need to be quick.

A couple of weeks ago, I posted a poll on the blog to see what versions of Excel we are all using. The poll is now closed, and the results make interesting reading.

First of all, the results themselves...

The question asked was, "Which version of Excel do you mostly use?"


Excel 2013 (Windows)
  44 (18%)
 
Excel 2010 (Windows)
  115 (49%)
 
Excel 2007 (Windows)
  43 (18%)
 
Excel 2003 (Windows)
  28 (11%)
 
Excel 2002 or earlier (Windows)
  2 (0%)
Excel 14.0 (Mac)
  2 (0%)
Excel 12.0 (Mac)
  0 (0%)
Excel 11.0 or earlier (Mac)
  0 (0%)

This is not a proper statistical survey, just something to give a bit of an insight into what visitors to the blog are currently using.

Only 18% of us are sporting the latest model, ensuring that we can take advantage of the most up-to-date features.

Almost half (49%) are mostly using Excel 2010. This includes myself as I find it provides me with the right balance of up-to-date features and compatibility with what others are using (particularly important when you are building spreadsheets for others). The poll suggests to me that it is still probably the right version for me to use when developing for others.

Excel 2007 is about as popular as Excel 2013, and given it's similarity to Excel 2010, I would expect it to have scored reasonably highly as there weren't too many reasons to make the move from 2007 to 2010 - if you'd already made the move from 2003 to 2007.

11% are still using Excel 2003 or earlier, which Microsoft have now ceased to support. There has been a lot of reluctance among users to upgrade from this version as it was very stable and did everything most users would want. The most visible change in the next version was to introduce the ribbon menus, which didn't add additional functionality but meant  a significant amount of training and/or experimentation was required to get up-to-speed on 2007 or later. I used as my main platform for development until quite recently.

As I mentioned in my earlier article, I think the withdrawal of Microsoft's support might be the justification to bite the bullet and upgrade. I know a number of users who have jumped from 2003 to 2013. The learning curve isn't really any greater than it would have been from 2003 to 2007.

Finally, as I suspected, very few of my readers are Mac users. I don't use a Mac myself, so don't really feel qualified to add much in the way of Mac related content. Obviously, much of the general Excel content applies and those who do use the Mac versions are very welcome on the blog. I know some of you do point out in comments when the Mac version does something different, which is much appreciated.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".