Search Not Just Numbers

Loading...

Tuesday, 15 April 2014

Microsoft withdraws support for Excel 2003 - Does it matter?

Last week, on the 8th April 2014, Microsoft withdrew support for Office 2003 and Windows XP, including, of particular interest to readers of this blog, Excel 2003.

The effect of this depends on two factors:

  • How many people/organisations are still using Excel 2003?
  • What are the implications for those that are?
I will try to answer the second question, if you can help me to answer the first by completing my poll at the top right of the blog.

Those still using Excel 2003 will already be facing many of the disadvantages of not having upgraded, such as:
  • Incompatibility with spreadsheets produced by colleagues, customers and suppliers (this one will only become a bigger issue with the withdrawal of support, as other users upgrade);
  • Lack of access to new features such as Tables and Structured References as well as improved functionality of existing features such as PivotTables and Charts.
However, the withdrawal of support from Microsoft raises some potentially more pressing issues.

Microsoft's description of the withdrawal states,

"After April 8, 2014, there will be no new security updates, non-security hotfixes, free or paid assisted support options or online technical content updates."

I would imagine that very few organisations, if any, are using Microsoft to support Office 2003 at this stage. One would assume that anyone with a direct support relationship with Microsoft would be using more recent versions. Similarly, there can be very few important technical issues not already addressed.

As a result, the biggest issue is "no new security updates"! This means that Microsoft will no longer provide patches to fix breaches of security in the software, leaving systems open to attack.

This should be a concern for those that have not been swayed by the other benefits of upgrading so far. So, maybe now is the time to go for it, if you haven't already.

Don't forget to complete the poll (at the top right of the blog), to let me know what version you are using.

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, 8 April 2014

Excel Tip: When case matters...

Just a very short tip for this week.

Do you ever have a situation where you need to compare two values but you don't want to treat them as the same if one is upper case and the other is lower case?

As far as Excel is concerned, "A"="a"!

Fortunately, there is a function in Excel that can help.

The EXACT function allows you to compare two values and returns TRUE if they are EXACTLY the same, including case.

So,

where

=IF("A"="a","The same","Different")

returns "The same"

=IF(EXACT("A","a"),"The same","Different")

returns "Different"

That's it! Simple, but handy if you need 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, 1 April 2014

Excel Tip: Identifying and removing duplicates

One of the biggest challenges when dealing with lists of data is spotting and/or removing duplicate entries.

Fortunately, the more recent versions of Excel offer some excellent tools for handling this problem.

I thought that this week, I would give you a quick introduction to them.

Highlighting Duplicates

Excel offers a quick facility to highlight duplicate entries in a list.

Simply highlight the list and, from the Home ribbon, select Conditional Formatting, Highlight Cells Rules, Duplicate Values.

You then get two drop-down boxes to choose whether you want to highlight the duplicates or the unique values and what colours you want to highlight them in. It's as simple as that.

This will simply highlight all of the cells that have a duplicate (or don't, if you select unique values).

Removing Duplicates

Often, you don't need to see the duplicates, just get rid of them. Excel also has a tool for that, which is also a little more sophisticated than the previous tool.

It doesn't just look at duplicate cells, but duplicate rows.

To use the tool, highlight all of the columns containing your data (data should be formatted correctly as covered in my earlier post on this subject, or at least with a column for each field and a row for each record).

On the Data ribbon, select Remove Duplicates and you will see the following dialog box:


First of all, check that the tick-box, My data has headers, is correctly ticked or unticked.

In the main window of the dialog box, all of the columns you have selected will be shown. and you can tick which ones must be duplicated to consider the entry a duplicate row.

In this example, with all columns ticked, the rows must be exactly the same before they are deleted. However, if we unticked the value column, then any rows with a duplicate code would be considered duplicates (irrespective of the entry in the Value column).

It is always the first row that is kept, and all subsequent duplicates deleted. This is only relevant if you have not ticked all of the columns, otherwise all of the duplicate rows are the same anyway.

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, 25 March 2014

Excel Tip: Converting numbers to text - retrospectively

No matter how much we think we know Excel, we still come across new things all of the time.

This is one a client showed me the other day - and it's a neat little trick!

How many times have you had a column of codes that Excel is reading as numbers and you need to be read as text (to work with a VLOOKUP or SUMIF for example)?

You can change the number format to text but this only partly solves the problem. It means any future numbers entered will be seen as text, but Excel only reads the existing codes as text once you have hit F2 and return on each one. I've found myself rattling down many a column like this in the past.

This little tip, however, means that I never have to do that again!

The approach involves using the Text to Columns feature that is designed for breaking text out into columns. The final step of the wizard, however, allows you to specify the format of each resulting column. This is how this tip works.

Simply follow the following steps:

  • Highlight the column of numbers;
  • Click Data, Text-to-Columns;
  • Select Delimited and click Next;
  • And Next again;
  • Select Text as the column data format;
  • Click Finish.
That's it - no more F2, Return, F2, Return, F2, Return, F2, Return, F2, Return.......

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".