Excel Tip: Capturing the current date and time

A reminder of a couple of looming deadlines before I get into today's post:

Firstly, tomorrow is the last day to complete the poll on which version of Excel you use. Please complete the poll (at the top right of the blog) so that I can understand what readers are using.

Secondly, Thursday (May 1st) is your last chance to get Mynda Treacy's Excel Dashboards course with a 20% discount. So, take a look now if you don't want to miss out.

OK, on with today's tip...

There are two ways you might want to capture and use the current date and/or time:

• Referring to the current date and/or time in a formula;
• Entering the current date or time in a cell

Referring to the current date and/or time in a formula

Excel offers two functions for this purpose.

The TODAY function evaluates to today's date and is entered as follows (it has no arguments):

=TODAY()

The NOW function works similar to today, but also includes the current time.

If you want to know how Excel handles dates and times, take a look at my earlier post on the subject.

Entering the current date or time in a cell

Sometimes, however, it is not a dynamic reference to the the current date or time that you want, but to enter a fixed version of the current date or time into a cell. The following two shortcuts will allow you to do this:

Ctrl+;   this enters the current date in the active cell

Ctrl+: (i.e. Ctrl+Shift+;) enters the current time in the active cell

These are great for if you want to log, say, the start and end times of an activity as you do it.

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

Excel Tip: Using Find and Replace to tidy up text (or formulae)

I hope everyone's enjoyed the Easter Break.

Just a quick and easy tip this week to ease us all back in gently!

I've written a number of posts in the past about tidying up text using functions, e.g. (Simple tips for tidying up text in Excel and Removing all spaces from text), but I thought I'd give a quick mention to a useful tool for a one-off tidy-up that can be very powerful (and also works within formulae).

The Find and Replace tool appears throughout Microsoft Office, but within Excel it can be even more powerful.

If you click the binoculars on the Home ribbon and choose Replace, you are presented with the following Dialog box:

This (by default) will search through the area of the worksheet you have selected (or the whole worksheet if you have not selected a range of cells first) and replace the text you enter in the first box with the text you enter in the second.

The following rules apply (assuming you do not change the options):

• It will include text within the cells (it does not need to be the whole cell contents)
• It will look within formulae too
• It is not case sensitive (however the replacement text will use the case you have entered)

These rules can be changed by making the options visible by clicking the Options >> button.

Here are some examples of how this tool can be used:

• Remove all spaces from text, by entering a space in the top box and nothing in the second box
• Replace an incorrect reference in a range of formulae, e.g. replace $A$1 with $B$6
• Remove commas from address data to make it usable as a csv file (e.g. replace , with space)
• Correct an incorrect formula, e.g. replace < with <=

With a bit of thought, many time-taking corrections can be speeded up considerably.

Before I go, I have a couple of updates to bring to your attention.

Firstly, if you haven't already given your answer in the poll at the top right regarding the version of Excel that you use, please do. At the time of writing, 16% of those responding still use Excel 2003, but that may be skewed by the topic of last week's post attracting more Excel 2003 users!

Secondly, Mynda Treacy's excellent Excel Dashboards course is once again available and, until 1st May, you can not only get a 20% reduction, but a free copy of my Introduction to Pivot Tables course absolutely free (just enter "Feechan" in the Referral Source field when you purchase, and email me a copy of the receipt.

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

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.

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

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.


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

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.

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