Monday, March 24, 2014

20 Excel Shortcuts You Need to Know. Number 15 is My Favorite.

Excel is a powerful tool, but you can’t call yourself a power user until you've mastered the essential keyboard shortcuts. I've mostly avoided the obvious and essential shortcuts that also work in other apps (such as Ctrl+Z for undo and Ctrl+C for copy, Ctrl+B for bold, etc.) but besides those here are the 20 Excel shortcuts you need to know:

1. F1: Access the Excel help file
Press F1 to access the Excel help file. Excel has hundreds of keyboard shortcuts so one of the most useful features of the help file is to search for ‘keyboard shortcuts.’ You'll find the full list of shortcuts there but the 20 listed here are the ones you’ll keep returning to.

2. Ctrl+`: Show or hide formulas
Not sure which formulas are running in your spreadsheet? Use Ctrl+` (the accent key, to the left of the number 1 key) to see the formulas in the cells rather than their results. 

3. Alt: Access the ribbon
Every single Ribbon command in Excel can be accessed via the keyboard. Hit Alt and you’ll see a letter (or a two-letter combination) above each ribbon tab. Type that letter or combo to use it. I've added the camera to the ribbon to quickly take screenshots, using Alt+4, as shown below:

4. Ctrl+;: Enter the current date
Using Ctrl+; saves time checking and entering the date. I find myself using this a lot. Please note this is a fixed date and not the =TODAY() function.

5. Ctrl+PgUp/PgDn: Navigate between worksheets
Complex Excel spreadsheets often have multiple worksheets. Rather than clicking on the bottom-of-screen tabs, use Ctrl+PgUp and Ctrl+PgDn to quickly navigate between sheets.

6. Ctrl/Shift+Space: Select an entire row or column
For even more selection power, Ctrl+Space selects an entire column. Shift+Space selects an entire row. You can then use the shift keys plus the arrow keys as appropriate to select additional rows or columns. Remember, C=Column=Crtl.

7. Alt+ =: Sum function
Here is the fastest way to sum your data in Excel: after entering your data in the column, click the first empty cell in that column and enter ALT+= (equals key), then click Enter. It will add up the numbers in all cells above.

8. CRTL+ UP/DOWN: Jump to top or bottom
This tip is particularly useful when you’re dealing with large number of rows. Use this method instead of endless scrolling to save time. Enter CTRL + ↑ (upward arrow key) to jump to the top cell or CTRL +↓(downward arrow key) to jump to the last cell before an empty cell.

9. Alt+Enter: Multiple lines in one cell (line break)
In some cases you may want multiple lines of data or text you typed into a cell to appear on several lines (also called inserting a line break). Instead of entering the text in another cell, press ALT+ENTER. That way you'll start a new line while typing or editing data.

10. CRTL+0/9: Hide columns or rows
To quickly hide a row / rows use CTRL+9. To hide a column / columns use CTRL+0. 

11. F6: Switch between tools
For all those anti-mouse users out there, F6 is the ultimate shortcut. It allows you to switch between the worksheet, the ribbon, task pane, and zoom controls.

12. CRTL + ‘: Copy cell above selected 
If you type Ctrl+' it looks at the cell above the selected cell and copies it into the current cell. 

13. ESC: Cancel changes
Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.

14. Shift + F3 : List of functions
Not sure if you need to use a SUMIF or COUNTIF? You know there’s probably a function for what you need to do but can’t remember the name? Open the list of available functions using Shift+F3. 


15. Crtl+g; Create bookmarks and other
Use Ctrl+g > Special to do things like Select all cells with comments, Select all cells containing formulas, etc. You can also create a bookmark within a large spreadsheet by naming a cell. Then use CTRL+g to quickly navigate to that cell.



16. CRTL+1: Format cells
Do you constantly find yourself formatting a cell by changing the font border and fill? Use CRTl+1 to display the Format Cells dialog box. 

17. F4: Toggle a reference
Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula (When NOT editing a formula, F4 is an alternative to Ctrl+y which is "repeat" or "redo").

18. CRTL+ALT+SHIFT+F9: Update formulas
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. Very useful when you have user defined functions. 

19. CTRL+SHIFT+~: Change date to number format
When Excel automatically formats a number as a date, you can change it back to a number using the shortcut CTRL+SHIFT+"~" (or change the date format using CRTL+SHIFT+#).

20. Alt + F11: Open macro editor
One of my personal favorite and most used Excel shortcuts is ALT+F11 to open the macro editor. Some of my most used macros are how to create folders from Excel and combine multiple Excel files.

Here's a quick summary of all the shortcuts that you can print off or Pin for quick reference later:

Learning to use keyboard shortcuts is one of the best ways to increase your productivity with Microsoft Excel. Are there any Excel shortcuts you often use that I’ve failed to list here? Please let me know!

Sunday, March 16, 2014

2014 March Madness Brackets in Excel

March Madness 2014 edition is here! Watching the NCAA Men’s Basketball Tournament is one of my favorite times of the year. Over the years I've created a large number of brackets and spreadsheets for other sporting events, like Superbowl Squares, NCAA Bowl Prediction Pool, heck even a horseshoes spreadsheet (and you can find all of my custom creations on the downloads page).
2014 march madness bracket in excel

However, I’ve never created an Excel bracket or pool manager for March Madness. Why? There’s been no need since discovering David Tyler’s bracket. If you’re planning on running a March Madness pool with Excel I highly recommend downloading and using David’s template as they’re simply the best Excel brackets around.

There's two files, a bracket and a pool manager. All players complete their own bracket in Excel and send to the pool manager. You save all the player's brackets in a folder then open the manager sheet. There's a built in macro that will import all the player's brackets into the pool manager. It's really awesome and easy to use. Instructions are included in the file. I really like how you can run different scenarios, like “if this team wins and this team wins then I’ll win the pool but if this other team wins and this other team loses then I’ve no chance at winning.”

Here's what the Excel bracket looks like, just click on each team's name to advance them:

2014 ncaa bracket excel spreadsheet

You can download David’s March Madness brackets for free here:

A few tips to help you complete your 2014 NCAATournament bracket:

  • A No. 16 seed has never beaten a No. 1. Like ever.
  • A No. 12 seed usually beats a No. 5 seed.
  • Odds of picking a perfect bracket: 1 in 9.2 quintrillion
  • Odds of picking a perfect bracket using historical data and basketball knowledge: 1 in 128 billion

If you have any questions about how to use the files feel free to let me know and I’ll do my best to help you out.

Monday, March 10, 2014

2014 Big Ten Tournament Bracket Spreadsheet

March is here which means March Madness is just around the corner! But before we can get to the field of 64, err 68, the conference champions have to be crowned. Since I support and live in Big Ten country I’ve made a 2014 Big Ten Tournament Bracket in Excel that you can download for free using the link below. Michigan, Wisconsin, Michigan State and Nebraska(!) are the number one seeds and get first round byes. I’ve included the complete schedule including game times and TV stations. If you’re not a Big Ten fan you can use my spreadsheet as a template to make brackets for the other conferences.
2014 Big Ten Tournament Bracket Spreadsheet

Download: 2014 Big Ten Tournament Bracket and Schedule.xls

Thursday, March 6, 2014

11 Best Excel Video Tutorial Channels

No matter how proficient you are with the computer, learning Excel is something that takes time, dedication, and the right teacher. You may have plenty of the first two, but finding the right teacher can often take huge amounts of time, especially if you live in an area that is far away from a major city.

Fortunately, learning Excel has never been easier, with so many resources available online. If you go to YouTube, there are a number of people who will walk you through basic and advanced features of Excel in easy to understand videos. I’ve made a few Excel video tutorials myself but admittedly haven’t put a lot of time into them. There are, however, some other individuals producing outstanding content. That being said, some Excel channels are simply a waste of time, so for your convenience I’ve listed below the 11 best Excel video tutorial channels (in no particular order) that will save you time and frustration when trying to learn how to use this amazing program.


11 videos tutorials to learn excel

HowCast Excel

For many people, Excel is something new that they might not even be really sure how to use yet. If this describes you, and you are looking for a great way to really understand Excel as an application before diving any deeper, HowCast has the way to do it. Their collection of very easy-to-follow guides will show you how to navigate specific aspects of Excel, so that you can begin using this program to the fullest extent possible.



Ilan Patao

If, like many people, you are trying to learn Excel to improve your efficiency at work, then this channel is a must. Ilan will walk you through many of the most important aspects of Excel in his videos. It's interesting to note that while not all of his videos are Excel-oriented, they all have a focus on taking care of business-related tasks. This is a welcome departure from many other Excel videos where all they show you how to do is to count apples or oranges.




Motion Training is a great resource for learning Excel. It makes this list because it has a very structured approach to helping Excel beginners quickly become skilled at working with Excel.



If you're looking for something more regimented, this YouTube channel is built around teaching you how to complete specific tasks in short, 3 to 4 minute videos. This is a great channel to look at if you are running into problems with a specific part of Excel and are looking for a dedicated video to show you how to get past it.


Bill Jelen a.k.a “Mr Excel”

You may find that when you're learning Excel, you might have a few questions about different aspects of the program, or how to complete something specific that you haven't yet found in a video. If that's the case, Bill Jelen is here to help you. He has an incredibly diverse YouTube channel consisting of over 1600 videos, many of them answering specific questions that his viewers have asked him about how to use Excel. This is a great place to go to for specific help on a question that actual users have.


Excel is Hell

If you’ve spent any time around Excel, you may feel the same way. This channel will help you figure out how to do some of the things that really make Excel difficult, while simplifying your life at the same time.


Excel is Fun

On the other side of the aisle from the last channel, you have people who think the exact opposite about this popular Microsoft program. Instead of focusing on solving the problems that make Excel very difficult, this channel will help show you how fun Excel can be, showing you some "magic tricks" that you can use to do incredible things in Excel that you didn't think were possible.




Of course, learning Excel doesn't just end at formulas and formatting; VBA is an extremely powerful scripting language that you can use to make Excel do pretty much anything that you want. If you've ever seen an incredibly powerful spreadsheet, especially one with buttons, it's likely that there was some VBA involved in making it. Visit this YouTube channel to learn more about how you too can integrate VBA into your Excel spreadsheets and take your Excel use to the next level.


Khan Academy

If you spent time learning Excel from some of the channels that we've mentioned, then you may be looking for a way for you that you can apply it in a more advanced manner. While the courses listed on this YouTube page are not specifically focused on using Excel, they will give you a great introduction to using Excel in other areas (specifically mathematically-oriented ones). Unlike many of the other channels, the Khan Academy courses are much more focused on theoretical exercises rather than helping you solve actual problems with Excel.


Danny Rocks

Whether you've been looking for a tutorial on how to create a dashboard in Excel, or you need an introduction to pivot tables or shortcuts, Danny is the person to teach you. He has a unique style that you will learn to love after watching a few of his Excel videos.


10 Minute Training

Maybe you only have a few minutes to spare on your lunch break, but that doesn't mean you can't learn something about Excel! All of these Excel videos are designed to take less than 10 minutes to watch, giving you important knowledge in a very truncated timeframe. While many of the videos on other channels are also under 10 minutes, this is one of the only channels that you will find where every video is specifically under this important hurdle.


No matter what your skill level is with Excel, these videos will teach you everything that you need to know about working with this incredible program. Whether you have one minute or 100, you can learn something about Excel by just taking a look at one of the channels provided.

Even though there are number of channels on the list above, you may find that one YouTube instructor caters more to your current knowledge base or teaching style (and is why I didn’t list them in any particular order, different strokes and all that). Even if you like one or more of the instructors right away, it's important to take a look at all of the channels to find someone who is right for you.

If you find this post useful I’d really appreciate it if you could please share it!