Tag Archives: Tips

That Damn Delete Key in Excel for Mac

Where is the delete button on Mac - ExcelI have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.

I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.

But all of that knowledge was shattered upon getting a Mac.

Where Is the Excel Delete Button on Mac

After some consternation, I learned where is the delete button on a Mac. To press the delete button on Mac computers you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action. 🙂

Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

Excel for Mac

Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

Not the kind of behavior that occurs in Excel for Windows.

How to Delete Cell and Range Contents in Excel for Mac

The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

Watch this 54 second video to see what I’ve been babbling about for the past 454 words.


YouTube link

Date Format Settings Excel Windows

Regional Date Formats in Excel

I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows and not Excel.

Trying to get Excel to automatically switch date formats between computers using different region settings was something I experienced a few years ago while working in England. Back then I made the mistake of ustng Excel to change the formatting for my dates. Not the way to go.

Regional Date Formats

The US Short Date format is represented in the form “m/d/yyyy” which means dates are shown in a “Months/Days/Years” format. Contrast that with Ireland, the UK, and most everywhere else, where the date format is “dd/mm/yyyy” and takes the form Days/Months/Years.
Regional Date Formats

Using the Default Short Date Format in Excel

Since I live in the US my computer’s regional settings are set for the US. When formatting dates in Excel for files that may be used internationally, it’s wise to use date formats that begin with an asterisk (*) per the Format Cells Dialog box.

Date Format Settings Excel Windows

Any date formatted in this manner will display correctly in another region. I can view the date June 13, 2011 in my spreadsheet as 6/13/2011 and send it to a person using a UK regional setting on their computer and the date will automatically display as 13/6/2011.

Nice and neat. We’re all speaking the same language here.

How Not to Change a Date Format for a Different Locale (Region)

Let’s say you are in the US and are designing a spreadsheet that will be used in the UK. You want to see “what they see” and decide to change the cell formatting in a column that contains dates. You select the range, open the Format Cells dialog box (Ctrl+1) then select English (U.K.) from the Locale (Location) drop-down list, and click OK.

Excel Local Date format UK

Bad move.

First of all, look at the picture above and notice there are no date formats that have a leading asterisk (*) which means that no date format will survive a change to a different region. You are stuck with this format in every region, whether it’s relevant or not.

How to Change Region Settings in Windows

The proper way to “see” a different region’s date format is to change your computer’s Region setting. Click the Windows Start button and select Control Panel. Find and click the link for Clock, Language, and Region.

Windows Regional Settings (Navigation to)

Next click the link for Change the date, time, or number format.

Windows Control Panel Regional Format Settings

On the Region and Language dialog box click the Formats tab.

Windows Region and Language Dialog Box

In the Format drop-down box select the Region of your choice. In my case that’s English (United Kingdom).

Windows Region English UK

In the screen shot above you can see the Short Date format is dd/MM/yyyy. Click OK to set your computer’s region.

Now open Excel and all dates previously formatted with the default Short Date format for the US (*3/14/2001) will show up in the Short Date format for the UK.

To check this fact, select a cell with dates, then bring up the Format Cells dialog box (Ctrl+1) to see that the Short Date format in Excel has automatically changed to *14/3/2001.

Windows Format Cells Date UK

This is how you change the date formats for different regions.

Customize the Region Date Format in Windows

I don’t like having two digits showing for the day and month in the Short Date format. To me 4 July, 2011 should be represented as 4/7/2011 and not as 04/07/2011. However that’s what you get when you select the English (United Kingdom) region setting.

I used a custom format for the UK region short date setting to rid my spreadsheet of the unnecessary zeros. To do this you bring up the Region and Language dialog box, as we did previously, and click Additional Settings… then select the Date tab.

Customize Regional Date Format 1

Type d/M/yyy in the Short Date box and click OK.

Customize Regional Date Format 2

Now all months and days with single digits show up without a leading zero in Excel when using the short date format.

Excel 2010 Clipboard Group

Ribbon Home Tab Comparison – Excel 2010 Windows and 2011 Mac

I’ll review each hard-to-find item on the Ribbon’s Home Tab in Excel 2010 (Windows) and let you know where they are located in Excel 2011 (Mac). I’ve even got some nifty pictures to speed along that understanding.

Note: The Ribbons in Excel 2010 and 2007 (Windows) are interchangeable, but I’ll only refer to 2010 below.

Each Ribbon Tab in Excel is organized by Groups. In Excel 2010 the Groups are shown at the bottom of the Ribbon. In Excel 2011 the Groups appear at the top, which I prefer.

The Excel 2010 Home Tab

The Excel 2010 Home Tab has the following Groups: Clipboard, Font, Alignment, Number, Styles, Cells, and Editing.

Excel 2010 Home Tab Split

The Excel 2011 Home Tab

The Excel 2011 Home Tab has the following Groups: Edit, Font, Alignment, Number, Format, Cells, Themes.

Excel 2011 Home Tab Split

Similar Items on Both Home Tabs

The Font and Number Groups have the same items so there’s no need to review them. The Alignment and Cells Groups have the same items, and although there is some variation in the underlying drop-down options I won’t cover them in this post.

Items You Have to Hunt For in Excel 2011

I’ll review each item on the Excel 2010 Clipboard and Editing Groups, which have items located on the Edit Group and elsewhere in Excel 2011, and finish up with the Styles Group that relates to the Format Group.

Note: The Themes Group in Excel 2011 doesn’t exist on the Home Tab in Excel 2010, but can be found on the Page Layout Tab.

The Clipboard Group

From left to right, the Paste icon is the first thing you see on the Excel 2010 Home tab. It resides in the Clipboard Group. Luckily the Paste icon is located in the same position on the Excel 2011 Home tab, but in the Edit Group.

The other items in the Clipboard group — Cut, Copy, Format Painter — don’t have a counterpart in the Excel 2011 Ribbon, but rather you can find them on the Standard Toolbar.

Excel 2010 Clipboard Group

The Editing Group

The Editing Group items are a mixed bag that can be hard to find in Excel 2011 unless you add some toolbar icons to the Standard Toolbar.

Excel 2010 Editing Group

The Fill and Clear icons can be found in the Edit Group. AutoSum is located on the Formulas Tab in the Function Group. Sort & Filter exists as two separate icons in Excel 2011 and are located on the Data Tab in the Sort & Filter Group with an icon for Sort and one for Filter. The Find & Select icon is not on the Excel 2011 Ribbon but located on the right side above the toolbar in a different format: the Search in Sheet box, which has a drop-down button that will bring up options for Advanced Search and Replace.

Excel 2010 Search Box Menu

In Excel 2011 you can find Sort and Filter on the Data menu, and Find can be found on the Edit menu but all of these are less than optimal because they take you away from the Home tab and require more clicks on the mouse or taps on the trackpad than are necessary.

As you can see in the Editing Group picture above, I’ve added AutoSum, Sort, and Filter icons to the Standard Toolbar to make these functions only one click away. This is a lot easier than hunting them each time.

The Styles Group

In Excel 2010 the Styles Group mostly corresponds to the Format Group in Excel 2011.

Excel 2010 Styles Group

The Format As Table icon in Excel 2010 is located on the Tables Tab in the Table Styles Group in Excel 2011.

Summary

The first time you use the Excel 2011 Ribbon Home Tab can be a trying experience if you’ve just come over from Excel 2010 or 2007. Hopefully this little summary with pictures is a help to those of you who, like me, get frustrated with Excel for making things so different between Windows and Mac.

Copy Data to Another Worksheet with Advanced Filter

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick.

The Problem with Advanced Filter in Excel

I was reading a post on the Contextures Blog, which indicated that you could use the Advanced Filter feature in Excel to copy filtered data to another worksheet. This feature has the potential to shorten some of my VBA code that essentially does the same thing. I mean, using Excel to do the work is simpler and usually faster than writing the code yourself.

So I created a simple worksheet with dummy data and tried out the Advanced Filter feature. In the picture below my List Range and Criteria Range are on Sheet1 and I’m just about to Copy to another location: Sheet2.

Advanced Filter Copy to Another Sheet

However, when I click the OK button the following message pops up.

Advanced Filter Warning

The Trick to Copy Filtered Data to Another Worksheet

My problem was that I didn’t know the secret. You have to initiate the Advanced Filter from the worksheet you are going to copy to. Duh, stupid me. So here’s how I defeated my nemesis, the Advanced Filter.

  1. Activate sheet 2 (where the data goes)
  2. Choose Data > Advanced Filter
  3. Select the List Range on sheet 1
  4. Select the Criteria range on sheet 1
  5. Select the radio button: Copy to another location
  6. Select the Copy to range on sheet 2
  7. Click OK

Here is the resulting data copied to Sheet 2.

Advanced Filter Results

There is a short video from Debra Dalgleish on how this is done. I’m too frustrated with Microsoft to pull off a video without disdain in my voice.

The problem I’m having is that this “work around” has been required for quite some time, with no updating of the Excel software code by Microsoft. (The video link above is for Excel 2003.) And yet in the latest two versions of Excel, 2010 and 2011, we still have to do something, that seems to me to be, completely counter-intuitive and un-productive.

Advanced Filter with VBA

I turned on the macro recorder to document the code required to execute, what I’m calling a “work around.” I then copied the first macro, modified the code, and ran it again from sheet 1 and it worked just fine.

Advanced Filter VBA Code

The first macro: CopyToOtherSheet does two things. 1) Selects Sheet 2 because that is what Microsoft requires, then 2) uses the AdvancedFilter method of the Range property to FilterCopy the CriteriaRange to the CopyToRange, with no Unique records.

The second macro: FilterCopyToOtherSheets only does one thing. It does what Microsoft should have done long ago, and that’s to copy the filtered range (sheet 1) to the copy range (sheet 2) while your on sheet 1.

The only code I changed (highlighted in red) was to eliminate the selection of Sheet 2, and add Sheets(“Sheet2”). to the CopyToRange. You can run this macro from Sheet 1 and the Advanced Filter will copy the filtered data to Sheet 2 with no problems.

Of course with static ranges this is a very limited macro, but my point here is that the code change should be minimal and if Microsoft can spend several billion dollars to get Windows 7 into Nokia phones, this shouldn’t cost to much to fix.

Okay, I’m done ranting.

UPDATE

Here is the file.

Excel 2011, 2010, 2007 format.

Excel 2003 format.

Helpful Links

Excel’s Advanced Filter Help Online

Sorting with Custom Lists in Excel

Custom List SortEvery once in a while I come across a sorting problem where a column of data needs to be sorted in a manner not consistent with ascending or descending order. Thankfully, Excel allows the sort order to come from a Custom List.

As an example I have some data with Date, Name, and Type as column headings, where the Type data can be one of five values: Breakfast, Lunch, Dinner, Snacks, Exercise. I want the data sorted by Date, in ascending order, then by Type, in the order listed above, not in ascending order: Breakfast, Dinner, Exercise, Lunch, Snacks, which is how I get the data.

In years past I would have solved this problem by creating another column (TypeCode) with a VLOOKUP formula keying off the Type data and referencing a new table created (on Sheet 1) with the sort order I wanted, as depicted below.

Sort Data with VLOOKUP Column

TypeCode is a VLOOKUP formula value that gives me the sort order I want, but with an extra column in my data and another worksheet with the lookup table. Too much extra stuff for this little problem.

Create a Custom List

A Custom List can be created in Excel 2010 by choosing File > Options > Advanced > Edit Custom Lists. In Excel 2011 choose Excel > Preferences > Custom Lists. Click Add then type the list entries — press enter or return to separate entries — then click OK.

Create a Custom List in the Sort Dialog Box

In our example we’re going to add a Custom List from the Sort dialog box. I select a cell inside my data then choose Data > Sort to bring up the Sort dialog box. As you can see below, the first level is sorted by Date from Oldest to Newest. The second level column is Type where I select Custom List… from the Order drop-down box, which will bring up the Custom Lists dialog box.

Sort Dialog Box Custom List Order

Here I click Add, and type in my list: Breakfast, Lunch, Dinner, Snacks, Exercise then click OK.

Custom Lists dialog Box Add

You now see the new Custom List in the second level Order column of the Sort dialog box where you can now click OK to have Excel complete the sort.

Sort dialog box Order by Custom List

The data is now sorted by Date (oldest to newest) and by Type using the Custom List.

Data with Sort

Sorting with a Custom List makes things simpler when Excel doesn’t have a default sort order to fit your needs.

A Woopra Chat About Excel

Woopra Desktop AppI use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.

My first chat happened the other day. The chat notice startled me, but I quickly recovered and was able to interact with a visitor.

(Bold and color are my additions for clarity)

Visitor #13357:
08:53:19. HI
You:
08:53:28. Howdy

Visitor #13357:
08:54:22. I WANT TO CONVERT 01:05 = 65 MIN WHAT I DO
08:55:42. IN EXCEL
You:
08:56:58. In cell A1 type 1:05, and in cell B1 enter the formula =A1*24*60 then change the cell format to General and you’ll see the 65.

Visitor #13357:
08:57:42. I NEED IN THIS FORMAT
08:57:46. 65 MIN
You:
08:58:41. Change the formula in B1 to =A1*24*60 & ” MIN”

Visitor #13357:
09:00:21. MY TIME FORMAT IN 01:01 AND IT SHOWS 60.9999999999999 MIN
NOT 61 MIN
You:
09:03:04. Try this =TEXT(A1*24*60,”#”) & ” MIN”

Visitor #13357:
09:04:38. THANKS ITS WORKING
09:04:44. THANKS A LOT
You:
09:04:49. Glad to help.

Woopra window

The strange thing I just noticed is that some of my replies have periods at the end. In a chat session no less!

Sorting Columns From Left To Right in Excel Causes Problems

Sort Left to Right ProblemYou may never have the opportunity to Sort columns of data from Left to Right, but beware of some apparent problems. A reader mentioned that when sorting from left to right the column width’s don’t change. I also found that formulas can be affected and for me that’s more problematic.

I was asked if Excel 2010 had fixed the problem with having column width’s stay the same when Sorting from Left to Right. I didn’t know so I constructed an example to find out. Note: Both Excel 2010 (Windows) and 2011 (Mac) work the same way for the examples that follow.

Column Width Problem

I used four column headings: ID, Date, Time, and Date Time with some random data, then formatted the cells and adjusted the column widths. I inserted a row above the data and entered the numbers 4, 2, 3, 1 above the headings and Sorted from Left to Right.

In the picture below, the worksheet on the left is Before Sort, and the worksheet on the right is After Sort. The Date Time and ID headings were swapped during the sort. However the column width did not change for column A or D, effectively rendering the Date Time unreadable.

Sort Right To Left Column Width med

Formula Problems in Sorting Left to Right

In these examples below there’s only one, simple formula. Essentially Date Time equals Date plus Time. (Date Time = Date + Time)

Formula Problem – Example 1

In this first example cells B3 and C3 have a blue dot and connected lines pointing to cell D3, which contains the formula =B3+C3 and is the active cell. The visual reference is provided by choosing Formulas > Trace Precedents while the active cell contains a formula.

Formula 1 Before

Formula 1 Before

I provided the same Left to Right Sort, swapping columns D and A during the process, and what to my wondering eyes did appear? A broken in formula for Date Time. (And eight tiny reindeer.) 🙂

The formula reference for cell A3 transformed to =#REF!+#REF! after the Sort was completed. Not the kind of thing you want happening.

Formula 1 After

Formula 1 After

Formula Problem – Example 2

In this second example I’ve corrected the formula in cell A3, which is now =B3+C3. This time I’ll Sort from Left to Right using Row 1 to sort from Smallest to Largest. The result will be columns A and D trading places. (Can I get a movie reference here? Don’t worry, I’m almost done.)

Formula 2 Before Sort

Formula 2 Before Sort

The spreadsheet below depicts what happened to the formula in cell D3, which is now =E3+F3 and obviously wrong again.

Formula 2 After Sort

Formula 2 After Sort

Formula Problem – Test Summary

With four columns, swapping one formula back and forth to see what happens could take some time. I compiled a small, sample summary table with 7 different sorts showing the Date Time formula before and after sorting the columns from left to right.

Summary Table Sort Left to Right Formulas

Col Move shows how the formula cell changed columns. In each case, after sorting the formula was incorrect. Some of the time there was one cell, either Date or Time, that was correct in the formula, but I didn’t find a combination that left the formula fully functional. I didn’t do an exhaustive combination so there may be some sorts that don’t break the formula, but I’ve made my point here.

Be very careful of formulas when sorting columns from left to right. And, oh yeah, you’ll probably be auto-fitting column widths as well.

Sorting Columns Left to Right in Excel

Sort Left to Right Theme PicOnce upon a time I had a master worksheet with data in predefined columns that was used for uploading data into a legacy system. Users would submit data in worksheets they developed, but oftentimes their columns didn’t always match that of the master worksheet.

So I used a little known option in the Sort feature of Excel to quickly rearrange their columns of data to match the master worksheet. This made it a snap to copy data from one worksheet to the other.

Default Sorting in Excel is top to bottom, but there’s another option to sort from left to right. Sort Left to Right Data BeforeHere’s a quick example on some bogus data so you can get a feel for how this feature works.

The column headers, from left to right are: Delta, Bravo, Alpha, Charlie. We want them to be in alphabetical order: Alpha, Bravo, Charlie, Delta.

Select a cell in the data range and choose Data > Sort. In the Sort dialog box, un-check the box for My data has headers.

Sort dialog box default settings

Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK.

Sort options dialog box

In the Sort dialog box, click the Sort by drop-down arrow and select Row 1. Make sure the Sort On selection is Values and Order selection is A to Z, then click OK.

Sort dialog box Sort by Row 1

The data is sorted left to right by using the first column.

Sort Left to Right Data After

Change the Example

Now let’s assume that we need the column order: Bravo, Delta, Charlie, Alpha. What to do? Well this is more like a real-life example that only takes a couple more steps to complete.

We need a blank row above the data, so right-click row 1 and select Insert from the pop-up menu. Enter 1 above Bravo, 2 above Delta, 3 above Charlie, and 4 above Alpha.

Sort Left to Right with row above

Now go through the same steps we did before, except the Row 1 sort Order will be Smallest to Largest.

Sort dialog box Sort Order Smallest to Largest

Once sorted you can delete the row above the header and we’re done. Excel has done all the heavy lifting.

Sort example 2 after

Now you can use this Sort feature to make your life easier.

Copy a Help Topic URL in Excel 2010

Excel 2010 Help is normally connected to Office.com so each Help topic has a specific web address (URL) that can be easily copied. This is only for Windows computers running the latest Excel version 2010.

You can check the connection status to Office.com by hitting the F1 key to bring up the Help screen, then look in the bottom right corner of the page to see the connection status. There will be one of two options showing.

  • Connected to Office.com
  • Offline

Help Connection Status Windows

Get a Help Topic URL

Make sure you’re connected to Office.com then use the keyboard shortcut F1 to bring up the Help window. In the search box type in something like Excel keyboard shortcuts and press the enter button.

Help Windows Screen

You’ll get search results (shown below) and to complete this example click the link for Keyboard shortcuts in Excel 2010.

Help Search Results

The topic will appear in the Help window (shown below). Right click on the topic title (highlighted in red) then select Properties from the pop-up box.

Help Topic Title Keyboard Shortcuts

In the Properties dialog box, triple click on the Address (URL) to select it, then use the keyboard shortcut Ctrl+C to copy the web address (URL) to the clipboard.

Help Topic Properties URL

Another method is to click the Address (URL) and using the keyboard shortcut Crtl+A to select the entire URL, then Ctrl+C to copy it to the clipboard.

You can use a URL copied to the clipboard in several ways.

  • Email it to someone
  • Use it as a hyperlink in Excel
  • Paste it into your browser to see the topic in a browser window
  • Use it in a web page as a link

Bonus Tip – Insert a Help Screenshot

Since Excel Help pops up in a separate window, it can be inserted into the worksheet as a picture. I find this to be useful at times to keep a bit of Help knowledge in the worksheet.

Here’s a couple of tips I use. First, size the Help window appropriately before inserting into the worksheet, you only get what’s showing in the window so make sure the text you want is visible. Secondly, after inserting the picture, you can use the Picture toolbar to Crop, Resize or modify the picture as you see fit.

To Insert a Help window screenshot:

  • Bring up a Help topic
  • Size the window
  • Make sure the text you want is showing in the window
  • Choose Insert > Illustrations > Screenshot
  • In the Available Windows drop-down click the Help window

Help Topic Screenshot Insert

I cropped the picture above with the Picture tools and could have done some fancy stuff, but that will have to wait for another day.

The Repeat Command in Excel for Mac

Adding the Repeat command icon to the Quick Access Toolbar in Excel for Windows is a simple process that I explained in a blog post last week. Excel for Mac — 2011 or 2008 — doesn’t have a Quick Access Toolbar, but you can customize the toolbars and menus.

Add the Repeat Icon to the Standard Toolbar

  • Right-click the toolbar and select Customize Toolbars and Menus…

Customize Toolbars and Menus

  • At the top of the dialog box select Toolbars and Menus
  • Select the Show checkbox for the Worksheet Menu Bar
  • In the Worksheet Menu Bar click Edit

Customize Toolbars and Menus

  • Select Repeat and while holding down the Control key, drag to the Standard Toolbar

Repeat icon on Standard Toolbar

  • Click OK

Strangely enough, if you select Commands at the top of the Customize Toolbars and Menus dialog box, then select Edit from the Categories window, there is no Repeat command in the commands window. This is why I thought it didn’t exist in Excel for Mac.

The Three Different States of the Repeat Icon

The Repeat icon becomes active and has color when the current command can be repeated. It will be grayed-out if the current command can’t be repeated. After there’s been an Undo action, the Repeat icon will change to a Redo icon.

Turn on Toolbar ScreenTips

Repeat Shortcut Key ScreenTipThere’s an option to show shortcut keys in the toolbar screen tips that’s nice to have turned on. It shows the keyboard shortcut for the Repeat command to be Cmd+6.

  • Right click the Standard Toolbar
  • Select Customize Toolbars and Menus…
  • Click the Show shortcut keys in ScreenTip checkbox

Shortcut Keys in ScreenTips

  • Click OK

Redo and Repeat Commands in Excel [Updated]

I always thought the F4 key was used to Repeat the last command, and the keyboard shortcut Ctrl+Y was used to activate the Redo command. I don’t use Redo very much, but the keyboard shortcut Ctrl+Z for the Undo command is one of my favorites. Toolbar icons for Undo and Redo command are common, yet I found there’s a toolbar icon for the Repeat command (Windows only).

QAT Undo Redo command
Adding the Repeat icon to the Quick Access Toolbar is a simple way to distinguish what commands can be repeated. The Repeat icon will be grayed-out if the current command can’t be repeated. It becomes active and has color (as shown above) when the current command can be repeated. After there’s been an Undo action, the Repeat icon will change to a Redo icon.

Add the Repeat Command and Redo Command to the Quick Access Toolbar (Excel 2010, 2007)

  • Right click the Quick Access Toolbar and select Customize Quick Access Toolbar…
  • Select Popular Commands in the left-pane drop-down list (All Commands works too)
  • Click inside the left-pane, press the R key, scroll down and select Repeat, then click Add
  • In the right-pane move the Repeat icon up to the position you want
  • Click OK

Excel: Repeat Last Action

Excel repeat last action

When you perform or use a command in Excel to repeat your last action that’s compatible with the Repeat command, its icon on the Quick Access Toolbar will be colored, as shown below. A very good way to know if the Ctrl+Y or F4 shortcut will work.

Repeat Tooltip

The exception is, that after you use the Ctrl+Z shortcut or the Undo command icon, the Repeat icon will change to be colored AND look like the Redo icon.

Repeat Tooltip as Redo

Example: I like some of my data table headings to be Bold and Centered. After doing so in the Format Cells dialog box, the Repeat command (F4 or Ctrl+Y) will apply those changes to other cells.

Install the Repeat icon on the Quick Access Toolbar and play around with it. You may discover a handy use for the Repeat command.

Excel for Mac [UPDATED]

The Repeat icon doesn’t does exist in Excel for Mac, but it will require a separate post to explain. However, you can change the F4 button to act like the Windows version.

Fixing the F4 Button in Excel for Mac

The F4 key in Excel for Windows is normally a shortcut key to Repeat the last command. In Excel for Mac the F4 key is assigned to the Paste command, which is driving me crazy so I decided to do something about it.

Default Excel F4 Keyboard Assignment in Excel for Mac

There are four keyboard shortcuts assigned to the Paste command in Excel for Mac — Shift+Del, Control+V, F4, and Command+V.

Excel Mac F4 Keyboard Shortcut

Overkill, don’t you think? So I’m thinking the F4 button can be reassigned with no problems, unless, of course, you’re in the habit of using the F4 key for the Paste command.

Reassign the F4 button in Excel for Mac

  • Select the Tools menu, and click Customize Keyboard…
  • In the Categories: box select Edit
  • In the Edit box, select Redo
  • Select the Press new shortcut key: box
  • Press the keyboard combination fn+F4 key (or just F4 key if you’ve setup the Mac function keys normally)

Reassign the F4 key in Excel for Mac

  • Click Add

Confirm F4 Change

  • Click OK to reassign the Paste command to the Redo command.

F4 key reassigned

The Excel F4 button will now work with the Redo command, which is similar to how Excel for Windows is setup.

Note: You have to make this change to each version of Excel for Mac. For instance the above procedure was done in Excel 2011, but my Excel 2008 version of Excel for Mac wasn’t affected and has to be done separately.

What Version of Excel am I Using?

If you’re curious about the version of Excel you’re using and don’t know how to go about it, you’ve come to the right place. It could be as simple as selecting the Help Menu and clicking About Microsoft Office Excel, but then again, maybe not. The five options below will help you sort things out.

Excel for Windows

If you’re using a Windows computer, chances are you’re using one of the three most recent versions of Excel.

Excel 2003

Select the Help menu, then click About Microsoft Office Excel.

About Excel Versions 2003

This also works with earlier versions of Excel (2002 and 2000).

Excel 2007

Select the Office button and click the Excel Options button, then select Resources from the left-hand pane and click the About button.

About Excel Versions 2007

Excel 2010

Click the File tab on the Ribbon, then select Help from the left-hand pane. The right-hand pane has the Version information.

About Excel Versions 2010

Excel for Mac

If you’re using a Mac the two most recent Excel versions, 2008 and 2011, just select the Excel menu, then click About Excel.

Excel 2008

About Excel Versions 2008

Excel 2011

About Excel Versions 2011

Use Function Keys Normally in Excel 2011

Since I’m relatively new to the world of Mac but an old hand with Windows, making an effort to discover Excel 2011 for Mac comes with some adjustments. Using the Function keys is one such irritation adjustment.

You see, using Excel on a Windows machine allows you to use the function keys, like F1, F2, F4, F5, and Alt+F11, which become second nature. But with a Mac you can’t just press, for example, the F5 function key all by its lonesome self. You’re forced to use the keyboard combination fn+F5.

You can change the Mac system preference to use all Function keys without having to press the fn key. Just go to System Preferences > Keyboard > select the Keyboard tab, and check the box to: Use all F1, F2, etc. keys as standard function keys.

Mac Function Key System Preference

Of course, all the function keys mentioned above don’t work with Excel 2011, with the exception of F5, which opens the Go To dialog box, and Att+F11 that opens the VBA Editor (even though the documentation doesn’t mention it).

And speaking of documentation, if you want a reference list of Function keys for Excel 2011 for Mac you can always look in the Excel Help system documentation, or just go to this link on the web and click on Function Keys. The list you see is considerably different than Excel for Windows, where you can view here.

Calculate Hours Between Two Dates and Times in Excel

Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.

Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.

A Date value in Excel looks like this: 40519
A Time value in Excel looks like this: 0.58333

Cell formatting changes how you see these numbers.

The Date: 7 Dec, 2010
The Time: 2:00 PM

How to Calculate Time Between Dates in Excel or the Duration Between Two Dates

If you want to calculate time between dates in Excel or the duration between two dates, you need to understand what they mean first. When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This a date serial number and it makes Date calculations easy.

You ask, “Why is this such a weird-looking number?” Well the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.

In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. (don’t ask) So the serial date 40519 represents 8 Dec, 2014 (Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system).

How to Subtract Time in Excel (Time Difference)

When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly.

Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM.

As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day.

How to calculate time difference in Excel

Dates and Times Together

In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.

So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.

You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833.

So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.

Calculating Hours Between 2 Dates and Times

If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time.  Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. (24 * 4/3 to be more precise)

Time Between Two Times / Dates

Finding the number of hours or the time between two times / dates is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours.

If you want to enter the dates and times separately (which is loads easier than typing in a date/time in one cell) then add the date/times together.

Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24

Here’s a look at a typical worksheet designed to calculate the hours between two dates.

Calculate Hours Between Two Dates and Times

As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2.

Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.

I hope this article helps you to better understand how to calculate time difference in Excel.

Related Posts Plugin for WordPress, Blogger...