Author: Shir Aviv

Ref 0001 Excel Shortcuts: How to Customize Mac Keyboard Shortcuts for Microsoft Excel

Download FREE, Printable Step-by-Step Guides

Step-by-Step Guide: Customize Mac Keyboard Shortcuts

  1. Go to System Preferences.
    • Shir Tip: Use Spotlight Search to open System Preferences.
      • Press COMMAND + SPACE to open “Spotlight Search.”
      • Type “sys” and it will most likely autofill “System Preferences”.
      • Press RETURN.
  2. Click on the “Keyboard” icon.
  3. Click on the “Shortcuts” tab on the top of the window.
  4. Click on “App Shortcuts” on the left panel.
  5. Click on the “+” (plus) button to add a new shortcut.
    • Choose “Microsoft Excel” from the “Application” dropdown list. NOTE: If Microsoft Excel does NOT appear in the list, you might need to select “Other” at the bottom and find it in your Applications folder.
    • Type in the exact name of the menu command you want to add. For example, if you want to create a shortcut to zoom, you must type in “Zoom…” with the 3 dots, since that is how it appears under the “View” menu in Excel.
    • Click in the “Keyboard Shortcut” text box, and then actually type the shortcut (it will fill in the correct symbols for the keys you are using. For example: ⌃⇧Q for CTRL + SHIFT + Q).
    • Click the “Add” button.
  6. Test the shortcut you just created to make sure it is working the way you want it to.
  7. Eat a plum, cause you are done! 🙂

 

Full Video Transcript:

Here are step-by-step instructions how to customize keyboard shortcuts on the Mac from Microsoft Excel.

Step 1 is go to your System Preferences. Once you’re here, go ahead and go to the Keyboard section.

Then you’re going to go on to the Shortcuts tab on top. Once you’re there, you’ll get a whole bunch of choices here on the left. Choose App Shortcuts, and then find Microsoft Excel Mac 2016.

If it’s not there, you’ll have to go ahead and hit a + and find the application in a list, and then actually make sure, this is where it gets tricky, type in the exact name of the menu command you want to add. Under File, under Edit, under essentially any of those top menu items, type it out exactly as it appears, even if there’s a “…”, that’s how you have to have it.

Once you do, you’ll actually use the shortcut, and it will generate the symbols for you. So that is how you can actually create the shortcut and have it save for you this way.

One last word of advice. Test out the shortcut you just made to make sure that it actually works before moving on. One cool tip to get to the System Preferences faster is to hit COMMAND + SPACE to get the Spotlight search where you can type in System Preferences, or even just “Sys”. Hit Enter, and it brings you to this Home section of the System Preferences.

That’s how you create custom shortcuts for Excel on your Mac.

0001 Excel Shortcuts: How to Open and Close Spreadsheets

If you’re like most people, you are starting your day off with this inefficient step. It doesn’t have to be this way! In this video, I will show you how to open and close spreadsheets using Excel shortcuts for both PC and Mac. Save time every single day, so you can get back to your life!

Download FREE Hands-On Exercises

Full Video Transcript:

If you’re watching this video, then odds are that you’re doing this one thing at least once every single day inefficiently. Today I’m going to show you how to open and close spreadsheets quickly.

The beauty of these shortcuts is that they’re all very simple and very straightforward. So when you want to actually create a new Excel file, all you really need to do is hit CTRL+N with the keyboard. N stands for new, doesn’t get easier than that. Same concept with CTRL+O for open, CTRL+S for save. Gets a little different with CTRL+W, think of it as closing the workbook or closing the window, which is actually different than quitting the whole application. I’ll show you in a second. So that is what you use ALT+F4 to quit the application, right. So if I hit CTRL+W, it closes that workbook or that window, but if I hit Alt+F4 on the PC, it quits the entire program. That’s the difference.

So what I want you to do is pay attention to that little nuance. And for the Mac, it’s actually going to be COMMAND+Q to quit, which is even more straightforward.

The memory trick here for the PC is think of it as at the altar, the Fantastic Four, those superheroes that we all love, are getting married and the priest is really fed up and says, “I quit.” So at the altar, the Fantastic Four getting married and priest says, “I quit,” that means ALT+F4 is to quit the application. And that’s it.

So for this exercise I want you to actually go ahead and create 5 new workbooks, save them all with silly names like 1 through 5, close them all, open them back up. And again, use only the keyboard shortcuts.

Don’t forget to visit excelshir.com where you can download these exercises along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac. Thanks for watching and I’ll see you next time. And in the meantime, don’t forget to share the Excel love.

How to Get Month Names from Month Numbers in Excel Using VLOOKUP

Practicing the most important Excel function: VLOOKUP!

“Hold your horses Shir! I don’t know how to do a VLOOKUP!”
No problem, check out this post: The Most Important Excel Function You Will Ever Learn

Alright sparky here’s the deal…

You’ve got month numbers and you want to show them as month names.

Simple enough right?

Sure you could do that by hand. Then again, you could also eat a bowl of rice one grain at a time.

OR

You could use a VLOOKUP! Or a fork. I think you’ll figure out which one goes where ;).

The secret is all in the setup.

Watch this video where I take you through the creation of the lookup table, and then the writing of the VLOOKUP function.

That way, every time there was a month number, excel will now spit out a month name right next to it.

[]

Key Takeaways:

  • When using VLOOKUP make sure to set the stage first. I recommend creating your Lookup Table of reference values in a separate worksheet called “Lookup Values.” Also, set it up so the Lookup Value (input value) is immediately to the left of your VLOOKUP function.
  • Don’t forget to anchor your reference to the Table_Array (Lookup Table) by pressing F4.
  • Don’t forget to type a “,” (comma) after each argument, especially before moving on to the col_index_num argument.
  • Use FALSE for the last argument of [range_lookup] to get an Exact Match. This ensures that if there is no exact matching value, we will know about it immediately by seeing a “N/A” error, which means “Not Available.”

Here’s an example spreadsheet so you can try it out for yourself:

Was this helpful? If so, don’t keep it to yourself! Spread the Excel love by sharing this with a friend.

Think you’ve got a cool use for VLOOKUP? Leave a comment and tell me all about it! It’s okay, I’m a hardcore nerd…I’ll appreciate it :).

Happy VLOOKUP practicing!

P.S. Got a quick Excel question? Click here for on call Excel help
P.P.S. Seriously, stop wasting hours on Google! Hop on a call with me and move on with your day!

The Most Important Excel Function You Will Ever Learn – Part 2

In the previous post (The Most Important Function You Will Ever Learn) we looked at the VLOOKUP Function in detail.

Now we’re going to take it up a notch, Emeril Lagassi style.

“So Shir, I’ve got a list of values and I want to know which range it falls under.”

Sounds like the perfect job for VLOOKUP Approximate Match!

If you haven’t already done so, take a few minutes and watch this tutorial on VLOOKUP first.

Okay, so remember the last argument which I said should just stay as False? Well, here’s what happens if you use TRUE, and why that’s important.

Suppose you aren’t dealing with a list of finite values like a product price list, a database of names and phone numbers, or a list of Simpsons characters.

Suppose instead you’re dealing with something that involves a range of possible values, like figuring out which tax bracket you belong to for example.

Which brings me to tonight’s WORD (anyone else miss The Colbert Report?). Just kidding, but it does bring us to this video.

In one of my first videos ever produced (can you tell?) I walk you through how to create a VLOOKUP Approximate Match to give you the next closest match, instead of giving you a nasty #N/A error.

Use VLOOKUP Approximate Match to look up an input value (that falls within a particular range) on a reference table, and spit out another value that’s associated with that range.

Used commonly for scientific experiments, when a resulting measurement falls within a particular range, and must be associated with a specific value or name for that range.

VLOOKUP Arguments Explained:

  • lookup_value = What value do you want to use to find other values?
  • table_array = Where is the lookup table of other values?
  • col_index_num = Which column number (relative to the lookup table) is the data you want to find located in?
  • range_lookup = Do you want an Exact Match (FALSE) or Approximate Match (TRUE)?

Note: Excel does not need columns 2 and 3 (the “High End” and “Range Description”), but I strongly recommend setting it up this way to make it easier to understand.

As a bonus, I’m also attaching a downloadable excel spreadsheet from the video so you can try it out for yourself:

If this was helpful, do me a favor and send this to someone who you think would benefit. You’ll be making their day, and mine!

Also, leave a comment if any part of VLOOKUP Approximate Match still mystifies you, or simply share a time when you used VLOOKUP Approximate Match and it made you happier than a kid on a snow day. Either way I’d love to hear from you!

Happy VLOOKUP Approximate Matching!

P.S. Got a quick Excel question? Click here for on call Excel help
P.P.S. Seriously, stop wasting hours on Google! Hop on a call with me and move on with your day!

The Most Important Excel Function You Will Ever Learn

FACT: Out of 400+ functions in Excel, VLOOKUP has been selected as THE indicator of Excel skills.

Don’t look at me, it wasn’t my decision.

So let me ask you this… Can you do a VLOOKUP?

If your answer is “no,” or a lukewarm “kinda sorta,” this video is perfect for you!

In this Excel VLOOKUP video tutorial, I break down all the 4 arguments of the function, and translate them into plain English (what a concept!)

Use the VLOOKUP function to look up an input value (usually an ID number) on a reference table and spit out another value that’s associated with it.

A common example is to look up a Product ID Number, and use it to find the price of that product from a price list.

VLOOKUP Arguments Explained:

  • lookup_value = What value do you want to use to find other values?
  • table_array = Where is the lookup table of other values?
  • col_index_num = Which column number (relative to the lookup table) is the data you want to find located in?
  • range_lookup = Do you want an Exact Match (FALSE) or Approximate Match (TRUE)?

Note: I highly recommend practicing this function over and over again until you get the hang of it. It’s quite challenging, but also extremely powerful.

As a bonus, I’m also attaching a downloadable excel spreadsheet from the video so you can try it out for yourself:

If this was helpful, do me a favor and send this to someone who’s about to go on a job interview, or spends a good amount of time in Excel. You’ll be making their day, and mine!

Also, leave a comment if any part of the VLOOKUP function still mystifies you, or simply share a time when you used VLOOKUP and it made your life 257% easier. Either way I’d love to hear from you!

Happy VLOOKUP’ing!

P.S. Got a quick Excel question? Click here for on call Excel help
P.P.S. Seriously, stop wasting hours on Google! Hop on a call with me and move on with your day!