Tuesday Tips (by YouTube!) – Data Validation

Welcome to the relaunch of Tuesday Tips!  I’m embarking on a new venture using video for these Excel tips.  Hopefully this will make implementing the tips a little easier.  So here for your enjoyment is the first YouTube edition of Tuesday Tips and covers a very handy Excel feature called Data Validation.  As always, let me know if you have any questions.


Tuesday Tips on hold

My fledgling feature Tuesday Tips has gotten off to a slow start, but certainly not for lack of content.  While going through the hundreds of tips roaming around in my head, I realized that many of them are far better explained visually.  I’m testing out a few different screen capture software programs and will hopefully be rolling out the Tuesday Tips feature in video in the next couple of weeks.  The purpose is to make the tips much more understandable so that you can implement them easier and making your Excel life that much simpler.  So stay tuned.

Tuesday Tip – Monday, Tuesday, Thursday, Wednesday

Tip #3 – Date formulas

When you want to enter a date into your spreadsheet, you have several options.   You can, of course, enter the date directly into a cell.  Or you can use the keyboard shortcut CTRL + ; which will enter today’s date into the active cell.  But suppose you have a file where you want the date to always show the current date.  You can enter the date in every time you open the file, but shouldn’t there be a way to avoid this step?  Indeed there is!  Enter this appropriately named formula into your spreadsheet:


With this formula, the date will automatically change whenever you open the file in a new day.  No more manually changing the date!

When working with dates, I’ll often need to know what day of the week I’m looking at.  In my line of work, I compare week over week numbers to analyze any trends.  But I have to be sure to look at the same day of the week.  I can’t accurately compare a Sunday with a Friday (apples to oranges, and all that).  Let’s suppose you’ve entered a date in cell A1.  You want to put the day of the week in cell B1.  We can take the hard approach by looking up the date in a calendar or perhaps trying to remember what day of the week a certain date was.  I don’t know about you, but whenever I try this last method, it very rarely works.  Instead, enter the following formula in cell B1:


This formula returns a number from 1 to 7, corresponding to the correct day of the week (Sunday = 1, Monday = 2, Tuesday = 3, etc.).  Now, you can enter a date in cell A1 and know immediately what day of the week it is.

BUT,” you say, “this result is still a little too cumbersome.  I don’t want to have it return a 1, 2 or whatever.  I want to know the day!”  No problem.  Sometimes having the numeric value for a day of the week is handy, but it certainly isn’t very pretty.  Still keeping your date in cell A1, enter the following formula in cell B1:


Notice that we’ve incorporated the same WEEKDAY formula from above into another formula.  When you have multiple formulas in one cell, this is called nesting. Now we introduced a new formula, the TEXT formula.  This formula takes the number given to us by the WEEKDAY formula and turns it into text.  The “dddd” (it must have the quotation marks around it) tells the formula that you want to know the name of a weekday.  So if your date in cell A1 was 12/21/2012, this nested formula would let you know that the Mayans have the world ending on a Friday.  TGIF?

That’s all for this week!  And remember, if you have any questions, feel free to leave a comment or send me an e-mail at eskypades[at]gmail.com

Tuesday Tips – Viva La Keyboard!

Welcome to the second installment of the brand new Eskypades feature, Tuesday Tips.  Last week we looked at a few keyboard shortcuts for formatting.  Today, I’ll show you a couple of navigational shortcuts.

Tip #2 – Getting around

While keyboard shortcuts used for formatting are handy, in my opinion they’re not nearly as handy as ones used to get around in Excel.  This is because I need to get from A to B more often than I need to make A bold, etc.  Some Excel users know the shortcuts for formatting but still rely on the mouse to select data, move from one sheet to another or from one workbook to another.  Remember, in using keyboard shortcuts, you’re trying to reduce your dependence on the mouse.  Here are a few that will hopefully get your started on your way to independence.  Viva la keyboard!

Moving between applications
I would be remiss if I didn’t tell you about what is perhaps the one keyboard shortcut I use more than any other.  I usually work with several different applications going at the same time.  For instance, right now I am running Excel, Word, Firefox, Outlook, and Windows Explorer.  While the bottom row of icons is helpful to show me what I have open, navigating these still involves using the mouse.  If I’m in Excel and want to send an e-mail, I can move my hand to the mouse, go down to the Outlook button, click and up pops Outlook.  Simple enough, sure, but what if I need to reference something in my Excel file or a Word document – or BOTH.  All that mouse clicking/hand moving back and forth gets old real quick.

The solution is of course, not to use the mouse.  Viva la keyboard remember?  To switch between open programs/windows, simply hold down the ALT button and then press the TAB button.  By using this keyboard shortcut, you can switch very quickly between any open window.  If you continue to hold down the ALT button, a small window will stay in the middle of your screen with an icon for every open window.  The way the window works is to keep the most recently used window next “in line” for use.  So if you switch from Outlook to Excel, you can just as quickly go back to Excel by pressing ALT+TAB, then pressing the two keys again to return to Outlook no matter how many other windows you have open.  Holding down the ALT button and pressing TAB will repeatedly will enable you to select an open program that wasn’t the more recently used.  Just remember to keep the ALT button pressed down while you press (and release) the TAB button until the window you want to work in is highlighted.

Moving between worksheets
There is a very similar shortcut that works only in Excel and Outlook (although I’ve not found much use for it in Outlook).  It allows you to cycle between open worksheets in Excel.  Think of it as the ALT+TAB’s cousin.  Instead of either going up to the Window menu option (Excel 2003) or the View–>Switch Windows option (Excel 2007), you can switch to another open worksheet, simply by pressing CTRL + TAB.  It won’t pop up a little window guide like the ALT+TAB shortcut does, but it works exactly the same way, starting the cycle with the most recent active worksheet.

That’s it for this week.  Come back next week to learn about some handy date formulas.  And remember — viva la keyboard

New Feature – Tuesday Tips!

Anyone who knows me well knows that I enjoy working with MS Excel.  Okay, maybe “enjoy” isn’t a strong enough word.  Let’s just say that I’ve learned to embrace my inner geek.  We hang out together and are pretty much best buddies, my geek and me.  Working in Excel is what I do nearly all day.  I love it.  And anytime I can share some tips with others and help their familiarity with Excel to grow, all the better.  So with that, I’d like to introduce a new feature of this blog called Tuesday Tips.  In it, I hope to highlight some handy Excel features that I’ve found quite useful or just plain cool.  And yes, Excel can be quite cool.  See?  There’s my geek again.  Along the way, if there’s a question you have about Excel’s features, feel free to ask.

Tip #1 – Use keyboard shortcuts for quick and easy formatting

While working in Excel, I find it much easier to use the keyboard for as much as I can.  This saves me from having to move my hands from the keyboard to the mouse to perform some function and then return them to the keyboard, only to go back to the mouse for something else, not to mention trying to find that button or menu option at the top of the screen.  If I can do the same task just as quickly – and sometimes quicker! – by using the keyboard, I’m all for it.

Microsoft Excel has many keyboard shortcuts, perhaps far too many to remember.  Many of these keyboard shortcuts apply to other Office applications such as Word or Outlook.  Most of the shortcuts will involve using the “Ctrl” key plus an additional key.  Several shortcuts are rather easy to remember because they combine the Ctrl key with the first letter of whatever you’re trying to do.  For example, if you want to make a word or number Bold, pressing Ctrl and “B” will do just that.  Here are a few keyboard formatting shortcuts that I use quite often:

  • Ctrl + C = Copy
  • Ctrl + V = Paste
  • Ctrl + X = Cut
  • Ctrl + Z = Undo
  • Ctrl + Y = Redo
  • Ctrl + B = Bold
  • Ctrl + I = Italicize
  • Ctrl + U = Underline
  • Ctrl + S = Save
  • Ctrl + N = New worksheet
  • Ctrl + ; = Insert today’s date (this one is very helpful, especially if you’re like me and can never remember today’s date.)

Shortcuts like these enable you to keep working without having to stop, right-click or go up to the menu to find the task you are wanting to perform.  Simply hold down Ctrl plus the appropriate key and “voila!” (“that’s French”), task done!  As mentioned previously, there is an abundance of keyboard shortcuts.  These are only a few of the more commonly used ones.  If you poke around Google long enough, you’ll probably discover that there is a keyboard shortcut for just about anything you want to do.  And if not, you can always make your own!  But that’s another tip for another day.

Free Microsoft Excel Seminar

“Increasing Your Productivity”

An Adoption Fundraiser event for intermediate Excel users.

For more information and to register, call Stephen at 540-580-7105 or e-mail eskypades@gmail.com.

Topics include:

  • Keyboard shortcuts
  • Named Ranges
  • Data Validation
  • Protecting & Sharing Workbooks
  • Data Manipulation formulas

Classes will be held in the Charles Gearhart Hall at Grace Church, 2741 Edgewood St SW Roanoke, VA, 2:00 – 4:00 p.m. and 7:00 – 9:00 p.m. on Thursday, May 20, 2010.  Please indicate which class time you would like to attend when calling or e-mailing to register.

So what DOES it mean, anyway?

{=IF(ISERROR(INDEX(DownRoute,SMALL(IF(DM=B$33,ROW(DM)-ROW(‘Route Information’!$B$25)+1,ROW(‘Route Information’!$B$37)+1),$A36),4)), “”,(INDEX(DownRoute,SMALL(IF(DM=B$33,ROW(DM)-ROW(‘Route Information’!$B$25)+1,ROW(‘Route Information’!$B$37)+1),$A36),4)))}

Since I’ve had a few requests as to what this formula means or does, I thought I’d try and explain. In a nutshell (where did THAT phrase come from anyway?), this formula looks at one set of data in a spreadsheet, matches that data to another set, and pulls a certain occurrence of that value. For example, in this case, it looks for a District Manager’s name in a range of cells (“DownRoute”) and then returns the occurrence of that name that is set in cell $A34. If $A34 says “2”, it looks for the second occurrence of the name and then returns the value in column 4 of the range. The formula performs similarly to a VLOOKUP formula, with the exception that a VLOOKUP formula can only return the first occurrence of the requested value. The end result of this particular usage is that it returns the start and end dates for when a particular district manager is on route so as to track the total number of days he or she is on route. Cool, huh?