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!

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).

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:

 

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

http://whistleblows.com/brackets/

 

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.

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.
 

Download: 2014 Big Ten Tournament Bracket and Schedule.xls

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.

 

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.

 

Link: https://www.youtube.com/playlist?list=PLA1EF3576D3E443C3&feature=plcp

 

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.

 

Link: https://www.youtube.com/channel/UCHF_ars_cUyE8T-T1zDGRxA

 

 

MotionTraining

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.

 

Link: http://www.youtube.com/user/MotionTraining?feature=watch

 

Contextures

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.

Link: http://www.youtube.com/user/contextures

 

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.

Link: https://www.youtube.com/user/bjele123/videos

 

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.

Link: https://www.youtube.com/user/ExcelisHell/feed

 

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.

 

Link: https://www.youtube.com/user/ExcelIsFun

 

ExcelVBAIsFun

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.

Link: https://www.youtube.com/user/ExcelVbaIsFun

 

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.

Link: https://www.youtube.com/user/khanacademy/search?query=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.

Link: https://www.youtube.com/user/DannyRocksExcels

 

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.

Link: https://www.youtube.com/user/10minutetrain

 

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!

 

How to Make Alternating Row Colors in Excel

I was at work the other day staring at endless rows and columns of data on a spreadsheet with my boss. She made a statement about how she “wished there was a way to separate the rows to make it easier to look at.” I smiled and said there was and sprang into action, putting my Excel skills into use. Using a simple formula in conditional formatting I made every other row a different color. She was impressed.

So here’s my tip to get alternating row colors when not using tables or shared worksheets. The trick is to use conditional formatting to apply a fill color to even numbered rows by using this formula:

 

=(MOD(ROW(), 2)=0)

How does it work? The function MOD returns the remainder after a number is divided by a divisor. The ROW function returns the row number of a reference. So the formula takes the current row number and divides by two. The resulting remainder is either a 1 or a 0. If we set the conditional formatting formula equal to 0 then every other row will be colored in. Make sense?

 

 

Here’s a short video showing exactly how to use it if you’re confused:
 

I hope that helps!

Weighted Winter Olympics Medal Count 2014

In honor of the 2014 Winter Olympic Games currently being held in Sochi, Russia, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2012 Summer Olympics held in London. There are two primary methods most websites appear to be ranking the 2014 medal count. Sites like Yahoo rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. Others rank by factors like per capita or GDP, which was used in a Freakonomics article about predicting the medal count with economics.

Pictured below is a bar chart showing all medals won for the top twenty countries (as of the time of this posting on 2-12-14). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.

The final 2014 Winter Olympic Medal Count

I've devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, the Olympic results aren’t as different as I thought they would be yet, but there are still a lot of medals to give out. See the results on Google Docs by clicking the link below:

Weighted Winter Olympic Medal Count spreadsheet

How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!

2014 NASCAR Fantasy League Template

A reader recently asked me for help with creating a NASCAR Fantasy League spreadsheet and I’m making a template of it available for download. I’ve created Excel templates for many other games and sports, including one for horseshoes, but this is our first NASCAR spreadsheet! This NASCAR fantasy game is based on Total Driver Points according to NASCAR’S scoring system. In order for your drivers to score they must be running for Sprint Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.

The sheet to track the scores for each race and driver respectively.

Here's how to play: At the beginning of the year (first race is Daytona on February 23rd) participants choose ten race car drivers. These will be their 10 drivers for the entire year - no changing after the entry deadline. All players also submit the three drivers that they think will place 1st, 2nd, and 3rd in the NASCAR standings at the end of the year. They don’t need to have these drivers in their picks, just who they think is going to finish on top. This is used as a tie-breaker.

Player ranking sheet.

The fantasy league manager manually enters each player’s picks into the spreadsheet once at the beginning of the year. They keep the same drivers all year long. After each race, the manager then goes to ESPN.com and enters the amount of points that each driver earned during the race into that driver’s column. The spreadsheet then automatically adds and ranks each player according to their driver’s scores. I used many of the same formulas found in the leaderboard of my NCAA Bowl Prediction Pool sheet. Can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula.

You can make each player pay a fee to play the game and split the pot to the top three winners. (1st=70%, 2nd=20%, 3rd=10%) Or many people have told me they like to use these types of games to raise money for charities, which is awesome!

To download the 2014 NASCAR Fantasy League manager simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it!

2014 NASCAR Fantasy League Template.xls download

If many readers play NASCAR fantasy games and find this useful I will improve the spreadsheet by automating the entry process and come up with a way to update the driver scores easier. This is the first version of the template and I don’t follow NASCAR myself so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!