Software

Office challenge: Can you get Excel's AutoFill feature to fill a range with just workdays - excluding Saturday and Sunday?

This week, show off your Excel expertise and learn the solution to last week's missing Save All command in the 2007 applications.
You probably know that you can use Excel's AutoFill handle to fill a range with the elements of a series. For instance, to fill a range with the days of the week, simply enter Monday into a cell. Then, click the fill handle and highlight the range you want to fill. Excel will fill that range with the days of the week from Monday through Sunday. Now, what if you want only the days of the work week -- Monday through Friday? Can you get Excel's AutoFill handle to do that? Last week we asked: Where's the Save All command in Office 2007? Happymedia_dz was the first to mention adding the command to the Quick Access Toolbar (QAT) -- and that's what I did as well. The instructions for adding Save All to the QAT follow:

  1. Click the drop-down arrow at the right end of the QAT and choose More Commands.
  2. Choose Commands Not In The Ribbon from the Choose Commands From drop-down list.
  3. Scroll down and select Save All.
  4. Click Add and then click OK.

I also mentioned that the Save All command isn't available in Excel. Your Excel users will get the same benefit if you add the following macro to the QAT:
Sub SaveAllWorkbooks()
  'Save all open workbooks.
  Dim wb As Workbook
  For Each wb In Workbooks
    wb.Save
  Next wb
End Sub
It's easy to add a macro to the QAT. Use the above instructions, except in step 2, choose Macro. Then, highlight the macro in question. Have you added any macros to the QAT? Consider starting a new thread and sharing those macros with the rest of us!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
A. C. Metcalf
A. C. Metcalf

Auto fill the Mon thru Fri, click on the Ctrl Key in Windows or the Alt-Op key in Mac, and continue to fill.

allin.potter@gmail.com
allin.potter@gmail.com

Just type in Monday, Tuesday, Wednesday, Thursday, Friday, then you can autofill to the right no problem. Or you can do that twice and autofill downward no problem.

jbaptiste
jbaptiste

I would use the autofill tool to insert the days click on the autofill options button and choose fill weekdays.

kirk.bare
kirk.bare

Type Monday or the day you want to start with in a cell. Drag the fill handle as far as you like. At the end an 'autofill' options box will pop up. Click on that, and you'll see .... Fill Weekdays.

inouyde
inouyde

Build a custom list MON,TUE,WED,THU,FRI. Works fine as long as you don't mind the shortened form of day's name

tim
tim

Drag the fill handle with the right mouse button down (right-click and drag). That gives you the autofill options box.

A. C. Metcalf
A. C. Metcalf

Hi, I actually tried that before I saw your post, but it didn't work for me. This happened in both (07) Windows and 08 (Mac). Can't understand what happened and "Help" is so wanting for real info but plan to keep trying to figure it out.

Schuylkill
Schuylkill

Thanks! I never tried that. Great time saver.

oxfordpauluk
oxfordpauluk

Fill list of Monday to Friday in normal way, then repeat below, finally select both weeks and then carry on dragging.

dogknees
dogknees

That seems like cheating to me. I read the question as meaning, I've already selected the first cell, I'm just about to click the fill handle and I decide I want only week days. I then select some option to get that result. Setting it up ahead of time is great, but doesn't seem in the spirit of the question. Though having said that I often get the context of these questions wrong. I always read a how-do-I that way. As "how do I modify the actions I'm about to take to get this alternative result" rather than "how do I configure things so I get this result in the future". The assumption is also that I'm not going to modify the normal behaviour for future actions, but select an alternative this time I carry out the action. For example. If I create a custom fill list for weekdays, then when I want all days starting on a Monday, I can't get it as Excel will see my custom list instead. That's what I mean about not modifying the normal behaviour.

TheProfessorDan
TheProfessorDan

with the alphabet. I had a situation where I was manually entering serial numbers into a spreadsheet and they ended in letters in alphabetic order.

stapleb
stapleb

As the next post tells you, drag with the RIGHT mouse button. When you release the button you get the shortcut menu, and from this select Weekdays. Don't think too many other posts mentioned the right mouse button. It is actually worth a try in many situations because of the shortcut menu you get when you release the mouse button.

ssharkins
ssharkins

You're right about the question -- I was specifically referring to using the AutoFill handle, but of course, other solutions are always welcome. Someone might have a better way to do something than the one I have in mind! So, by all means, feel free to provide a solution even if it doesn't seem to be exactly the one I'm looking for!

ssharkins
ssharkins

The left button works fine, if the option is enabled. Here's how to check: From the Tools menu, select Options. Click the Edit tab. Make sure the Show Paste Options Buttons is checked. Click OK. If this setting is checked, the left button displays the Auto Fill Options. If this setting is disabled, the left button won't, but the right button will -- so you can disable it and then force it only when you choose.

Editor's Picks