Excel Dates Won’t Change Format

Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won’t change format? And, if you try to sort that column of dates, things end up in the wrong order.

That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool.

Continue reading “Excel Dates Won’t Change Format”

Highlight Upcoming Employee Service Anniversaries

Here at Contextures, there aren’t very many employees to keep track of. There’s just me, and I always remember my hire date anniversary, and take myself out for a nice lunch. However, you might have more people on your employee list, and you can use Excel to highlight upcoming employee service anniversaries.

Continue reading “Highlight Upcoming Employee Service Anniversaries”

Show Temperature in Excel Color Scale

How hot was it in your neighbourhood last week? It was so hot, here in Mississauga, that I was afraid to stay outside for more than a minute, in case I melted, or burned to a crisp! On the bright side, I got lots of work done, but I’d rather have cooler temperatures, than hot, muggy days.
With Excel, I can decide when it’s better to stay inside. When I think of hot temperatures, red comes to mind, and blue is what I think of for cold days. So, to show the daily temperature by colour, I created a worksheet with a conditional formatting color scale.
This feature is available in Excel 2007 and later versions, and I’ve used Excel 2013 in this example. You can see the steps in the video at the end of this post, and written instructions are below.
colorscaletemperature02

Show the Temperature by Colour

Here is how I set up a worksheet where I enter the current temperature, with a colour code to highlight hot and cold days.

  1. Enter the temperature in cell B3
  2. In cells F6:F25, enter the numbers 140 to -50, as shown below
  3. In cell G6, enter a formula that will show an empty string if the current temperature is equal to the temperature in that row, or between that temperature, and the one above:=IF($B$3=F6,””,IF(AND($B$3>F6,$B$3<F5),””,F6))
  4. Copy the formula down to row 25
  5. Select cell B3, then press the Ctrl key and select cells G6:G25 (the cells with the formulas)
  6. On the Ribbon’s Home tab, click Conditional Formatting
  7. Click Color Scales, then click on the Red – White – Blue scale
  8. Change the temperature in cell B3, and the cell color will change, based on the color scale.
  9. (optional) Hide columns F:G, so the color scale is not visible.

colorscaletemperature01

Remember the Excel Tips Book Giveaway

The Excel Tips book giveaway is open until Wednesday, July 24th, at 12 noon Eastern time, so remember to enter, if you haven’t already. Just go to the blog post and add your tip in the comments. Come back on Thursday, July 25th, to see if you won. Good luck!

Download the Sample File

For more information on conditional formatting, and to download the sample file, please visit my Contextures website: Conditional Formatting Examples Page.

Video: Show Temperature in a Color Scale

To see the steps for showing the temperature with a color scale, please watch this short video.

______________

Highlight Duplicate Records in an Excel List

With Excel’s conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.

Highlight Duplicates

In this example, we want highlight the duplicate rows in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.
highlightduplicatesribbon
So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.

Concatenate the Data

In the sample data, there are two identical rows, and these should be highlighted after we apply our conditional formatting.
highlightduplicaterows03
The first step is to use the CONCATENATE function to combine all the data into one cell in each row. Add a new heading in cell G1 – AllData – and in cell G2, enter this formula, to combine the data from all the cells in that row.
=CONCATENATE(A2,B2,C2,D2,E2,F2)
highlightduplicaterows04
Next, copy the formula down to the last row of data.

Apply the Conditional Formatting

Then, a conditional formatting rule is set, to color the rows that are duplicate records. We’ll use the COUNTIF function to check for duplicates in the AllData column.
=COUNTIF($G$2:$G$8,$G2)>1
highlightduplicaterows01
If there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured. The two rows with duplicate records are highlighted, so our conditional formatting formula worked!
highlightduplicaterows05

Download the Sample File

For detailed instructions, and to download the sample file, please visit my Contextures website: Highlight Duplicate Records in a List



Watch the Video

To see the steps for setting up the conditional formatting, please watch this video tutorial.

__

_________________________
Save

Keep Numbers Aligned When Zooming

Who knew that this would still be a problem in Excel 2013? Almost 4 years ago, I posted about numbers not lining up, when Excel was zoomed to less than 80%

Now I’m working on a new laptop, and realized that the problem is back. In Excel 2013, I’m using the default font – Calibri 11. Here’s a list of numbers, at 80% zoom.

numberfont01

And here is the same list at 72% zoom – the numbers have changed to a proportional font, and the 1111111s are much narrower than the 8888888s.

numberfont02

Change the Registry

To fix the problem, I followed the instructions that I posted in April 2009, when I was setting up my previous laptop. Here they are again, with adjustments for Windows 8 and Office 2013. The original instructions came from this MSKB article:

Euro Currency Character Is Not Displayed Correctly in Excel 2003

This is really a fix for a Euro symbol display problem, but it also fixes the proportional font display.

Here’s what I did for Win8 and Excel 15:

  1. Make a backup copy of the registry before you tweak any settings
  2. Quit any programs that are running.
  3. Press the Window key and R, to open the Run window.
  4. In the Open box, type regedit, and then click OK.
    • numberfont03
  5. Locate, and then click to select the following registry key:
    HKEY_CURRENT_USER/Software/Microsoft/Office/15.0/Excel/Options
  6. With the Options key selected, point to New on the Edit menu, and then click DWORD (32-bit) Value.
    • numberfornt05 
  7. Type FontSub, and then press ENTER.
  8. Right-click FontSub, and then click Modify.
    • numberfont06
  9. In the Value data box, type 0. Since the value is zero, it doesn’t matter which Base you select – I left it on Hexadecimal.
    • numberfont07
  10. Click OK to close the Edit DWORD window
  11. On the File menu, click Exit to quit Registry Editor.
  12. Start Excel, and the numbers should line up correctly, eve when zoomed.

numberfont08

___________________

Midnight Times Missing in Excel Worksheet

Last week, I was working on a client’s time sheet file, and noticed something strange. The worksheet was used to record shift start and end times, and I was testing the calculations, to make sure that everything was working correctly.

Shifts that run past midnight can cause problems, so I tested that scenario, and I also tested shifts that started at midnight. I wanted to be sure that everyone would get all the pay they were entitled to!

In column D, I calculated the hours worked, by subtracting the start time from the end time, and adding 1 to the end time, if it was in the next day. Here is the formula in cell D2:

=(C2+IF(OR(C2=0,C2<B2),1,0)-B2)*24

showzerovalues02

Missing Midnight

The calculations were working well, but the cells where I had entered a midnight time appeared empty. Fortunately, the calculations still worked, even with the missing start times.

showzerovalues03 

I know that midnight is a favourite time for mysterious things to happen in horror movies, but was my worksheet haunted? Was this an early Halloween prank?

Hidden Zeros

Then I realized that someone had formatted the worksheet to hide the zeros. And, if your worksheet is formatted to hide zeros, the midnight times won’t show, because they are equal to zero – 0:00

I turned the Show Zeros setting back on, and all the midnight times came out of hiding.

To show zeros on a worksheet:

  1. In Excel 2010, click the File tab, then click Options.
  2. In the Advanced category, scroll down and in the Display options for this worksheet, add a check mark to "Show a zero in cells that have zero value"
  3. Click OK to close the Options window.

showzerovalues01 

Unhide Your Zeros

I had forgotten all about this problem, until someone left a comment on my blog yesterday, asking why the midnight times weren’t showing in her worksheet. At least I’m not the only one who has had this mysterious problem, and if it has happened to you, I hope this tip helps.

It’s not one of Excel’s most complicated problems, but it’s the little things that can drive you crazy. 😉

____________________________

Conditional Formatting in Excel 2010

If you’re upgrading to Excel 2010, you’ve probably noticed that the Conditional Formatting feature has changed quite a bit. Now you can apply more than 3 rules, and there are fancy features like data bars and icon sets.

conditionalformaticon00

Intro to Conditional Formatting

I’ve updated my Intro to Conditional Formatting page, to show Excel 2010 instructions, and made a new video to show the steps. If you’re looking for Excel 2003 instructions, they’ve been moved to this page: Excel 2003 instructions.

Watch the Excel 2010 Conditional Formatting Video

To see the steps for creating two conditional formatting rules in Excel 2010, please watch this short video.

 

Download the Sample File

To see the sample used in this video, and other conditional formatting examples, you can visit the Conditional Formatting Introduction page.

___________________

Create Alternating Shaded Rows on Excel Sheet

If you’re using a named Excel table, you can apply a style that shades alternate rows with colour. In the table shown below, the row shading has two rows of grey followed by one row of white. To create this table style, I duplicated one of the existing styles, and modified the row shading.
condformatbandedrows01
If you don’t want to use a table, or table styles aren’t available in your version of Excel, you can still have shaded rows, by using conditional formatting.

Shade Rows With Conditional Formatting

To shade the rows, we’ll use the MOD function in a conditional formatting formula. To see how it works, we can test the MOD function on the worksheet, in column G.
We want a set of 3 rows – two with shading, and then a row with no colour. With the MOD function, we’ll get the remainder, if the row number is divided by 3.
=MOD(ROW(),3)
The result for each row is shown in column G, and is either 0, 1 or 2.
condformatbandedrows02
We can shade rows where the result is 0 or 1, and leave the rows with 2 as no fill colour. To check the result of the MOD function, we can add a formula in column H, to see if it’s less than 2.
=G2<2
condformatbandedrows03
If the result is TRUE, we can shade the row. I’ve highlighted the TRUE cells in the screen shot above, to show which rows will be shaded.
Columns G and H were just used to test the formula, so I’ll clear those out, before formatting the cells.

Create a Conditional Formatting Formula

To shade the rows, we’ll combine the MOD function and the test of the results, in a conditional formatting formula.

  1. Select the cells where you want the banded rows to appear.  In this example, cells A2:F9 are selected.
  2. On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
  3. For Rule Type, click on Use a Formula to Determine Which Cells to Format
  4. For the formula, enter    =MOD(ROW(),3)<2
  5. Click the Format button.
  6. On the Patterns tab, select a colour for shading – light grey was used in this example
  7. Click OK, click OK

condformatbandedrows04
The selected range has two rows of grey shading, followed by a row with no fill colour.
condformatbandedrows05

Make the Shaded Rows Adjustable

In the previous example, the numbers 3 and 2 were typed into the conditional formatting formula. To make the shading adjustable, you could enter the numbers on the worksheet, and then refer to those cells in the formula.
On the worksheet, create an input range for the numbers. In this example, the numbers are entered in J1 and J2, and the sum of those numbers is in J3.
condformatbandedrows06

Modify the Formula

To change the conditional formatting formula:

  1. Select a cell in the range where the conditional formatting rule was applied.  In this example, cells A2:F9 are selected.
  2. On the Ribbon’s Home tab, click Conditional Formatting, then click Manage Rules
  3. Click on the MOD rule in the list of rules, and click Edit Rule
  4. Change the formula, so it refers to the grey shading number — $J$1, and the total number — $J$3:
    • =MOD(ROW(),$J$3)<$J$1
  5. Click OK, click OK

condformatbandedrows07

Test the Shading

To test the interactive shading formula, change one or both of the numbers in J1:J2. The shading on the worksheet should automatically adjust.
In the screen shot below, the numbers were changed so there is one grey row, followed by 2 rows with no fill.
condformatbandedrows08

Watch the Video

To see the steps for creating shaded rows on the worksheet, please watch this short video tutorial.

Download the Sample File

To download the sample file, please visit the Conditional Formatting Examples page on my Contextures website. The Excel 2010 / 2007 sample file contains the interactive example.
______________________________

Lock Excel Heading Rows in Place

If you want to scroll down the worksheet, and lock the heading rows in place, so they’re always visible, you can use the Freeze Panes command. Be careful though, or you might end up with hidden rows that you can’t get to.

Freeze Panes Settings

In Excel 2010 and 2007, there is a Freeze Panes command on the Ribbon’s View tab. You can select one of the following commands, to freeze the rows and/or columns in place.

  • Freeze Panes
  • Freeze Top Row
  • Freeze First Column

freezepanes03

For Excel 2003 instructions, visit the Contextures website: FAQ: How do I lock the Title Row?

Freeze Top Row

If you select the Freeze Top Row command, the top visible row in the Excel window is frozen. You will not be able to scroll up to see any rows above the frozen row.

freezepanes04 

Freeze First Column

If you select the Freeze First Column command, the leftmost visible column in the Excel window is frozen. You will not be able to scroll left to see any columns to the left of the frozen column.

It doesn’t matter which cell is active when you apply the Freeze First Column command.

freezepanes05 

Freeze Panes

If you select the Freeze Panes command, the following table shows which rows and columns will be frozen, based on what you’ve selected on the worksheet, and which cell is active.

Selection Active Cell Frozen Rows Frozen Columns
Cell(s) A1 All rows above the centre of the Excel window All columns to the left of centre in the Excel window
Cell(s) Any cell except A1 All rows above the active cell All columns to the left of the active cell
Entire row(s) A1 All rows above the centre of the Excel window All columns to the left of centre in the Excel window
Entire row(s) Any cell except A1 All rows above the active cell None
Entire column(s) A1 All rows above the centre of the Excel window All columns to the left of centre in the Excel window
Entire column(s) Any cell except A1 None All columns to the left of the active cell
Entire worksheet A1 All rows above the centre of the Excel window All columns to the left of centre in the Excel window
Entire worksheet Any cell except A1 All rows above the active cell All columns to the left of the active cell

Prevent Mysterious Hidden Rows

Sometimes you want to hide rows on the worksheet, but it’s also possible to hide rows unintentionally, when you freeze the headings.

Before you freeze the title rows, make sure that all the rows and columns in the area to be frozen are visible, if you want to see them after freezing. For example, if Row 1 is out of view, you won’t be able to scroll up to that row later, if you freeze the rows.

In the screen shot below, there is a date in cell H1, and you can use the arrow keys to move to that cell, but can’t scroll up to see the row.

freezepanes02 

Follow the steps below to unfreeze the panes, then make sure all the rows and columns that you want in the headings are visible, before you freeze them again.

Unfreeze the Headings

If you need to reset the freeze panes, or don’t need them frozen any more, you can remove them.

  • On the Ribbon’s View tab, click Freeze Panes, then click Unfreeze Panes.

freezepanes06

Watch the Video

To see the steps for freezing the headings with the Freeze Panes command, and the problem with hidden rows, please watch this short video tutorial.

__________________