Wednesday, April 23, 2014

Excel Help Milestone: One Million Pageviews!

Today we reached a milestone here at Excel Spreadsheets Help blog - we've now had over ONE MILLION pageviews! Yeah, it’s taken a few years to reach this point but it still feels like a huge achievement. I started this blog in December of 2009, almost five years ago now - a lifetime in internet years. While I know there are thousands of other websites out there with much MUCH higher traffic, reaching this milestone made me quite happy, as this is a blog only I have worked on and just in my spare time.

At the beginning of this year I recommitted myself to this blog. I promised to deliver the same templates I always do (all the yearly sports spreadsheets) but also to post more tips and tricks that many of you have asked for and is why I wrote about the 11 best YouTube channels to learn Excel and Excel keyboard shortcuts you need to know. There’s many more Excel tips in the pipeline too.

I noticed there are now 220 articles on this blog (equivalent to the length of a 600-page book!), but looking in my Drafts folder, I see there are many unwritten ones waiting for further details or the right time to share them. More of them get added every day, sometimes originating as part of email conversations I have with many of you.

Thus, I want to take this time to thank each and every one of you. Without you and your comments, and kind emails, I never would have reached this point. Without your encouragement I would've quit a long time ago so for that I can’t thank you enough! Here’s to the next million pageviews!

Wednesday, April 16, 2014

2014 NBA Playoff Bracket in Excel

The regular season is over and it's time to download your 2014 NBA Playoff Bracket Excel file. The NBA playoffs start Saturday, April 19, three days after the end of the regular season today. All first games of the eight first round matchups will be played either April 19 or 20. Sixteen of the 30 league teams make the playoffs, eight from each conference. This produces eight first round matchups and no team gets a first round bye. Each matchup in all rounds is a best 4 out of 7 series in this format: HHAAHAH. Download the basketball bracket using the link below:

2014 nba playoff bracket in excel

  Remember, the NBA finals start Thursday, June 5. I'm looking forward to it, how about you?

Monday, April 14, 2014

2014 NHL Playoff Bracket

The NHL playoffs start Wednesday, April 16, three days after the end of the regular season. Sixteen of the 30 league teams make the playoffs, eight from each conference. Per conference, the top three finishers of both divisions and two wildcard teams make the playoffs. This produces 8 first round matchups. There are no first round byes. All rounds are best 4 out of 7 in this home and away pattern: HHAAHAH. Here's what the matchups look like this year:

2014 nhl playoff bracket

The 2014 NHL playoff schedule is included in the spreadsheet and lists the dates, times, and national broadcast information for the first round of the 2014 Stanley Cup Playoffs. Download the bracket for free by clicking the link below:

2014 NHL Playoff Bracket.xls download


I'll be cheering for the Columbus Blue Jackets. Who are you rooting for?

Monday, April 7, 2014

Unique Excel Uses: Writing a Book

If you haven’t been able to tell by now I’m a huge fan of amusement parks and roller coasters. Huge fan is probably an understatement. I’ve noticed top ten type lists are hugely popular right now (which is why I created the top 11 Excel YouTube channels and top Excel shortcuts) so I decided to write a short ebook about the top 50 most terrifying roller coasters ever built. My idea was to list each coaster with some stats then share my reasoning for why it’s so terrifying. Since I wanted the same format for all fifty coasters I knew I would be doing a lot of copy and pasting. That’s when I decided to put my Excel spreadsheet and programmings skills to use.

Where many authors begin writing in Microsoft Word or some other Word processor I started in Microsoft Excel. Pictured below is the table I created with all the relevant stats:

After data is entered for all fifty coaster I run a macro that export the information to Microsoft Word in the format I wrote into the code. An example of one of the coaster is shown below:

Known for: Nurse in the station
Park: Crystal Beach
Location: Canada
Type: Wood
Opened: 1926
Closed: 1946
Designer/Manufacturer: Harry G. Traver
Height (ft.): 96
Drop (ft.): 90
Speed (mph): 60
The Crystal Beach Cyclone is considered to be the most extreme roller coaster ever built. The mother of all roller coasters spawned in 1927 at Crystal Beach Amusement Park in Ontario, Canada. For over twenty years this demonic creation terrorized over five million victims, dragging them through its demented dives, twisted turns, and wickedly warped trackage. While its ninety-six foot tall lift hill may be relatively small by today’s standards the Cyclone made up for lack of height with rapid fire transitions and perverted elements, such as eighty-degree banked turns, a high speed figure-eight, jazz-track (trick track), and tiny one foot bunny hops. Although this terrifying trip lasted only around forty seconds after cresting the lift there was almost no straight track to be found and the unrelenting pace made the ride so severe it was probably more than enough for most riders.

The macro outputs all fifty coasters. Now, all I have to do is add the front matter (title, index, copyright information, dedication, etc.), resources, about the author, and insert my pictures and it’s done. From concept to publication took a total of four weeks, working all in my spare time (so to those of you who think you don't have time or the skill to write an ebook, I think you can do it too)! Check out the completed book on Amazon (borrow for free if you're a Prime member).
This same technique can be applied towards websites, blogs, papers, and a great number of other tasks. What unique projects have you done in Excel?

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