I'm giving away 4 copies of Bill Jelen's newest book, MrExcel XL: The 40 Greatest Excel Tips of All Time.
Note: The contest is now over, but checkout my review of this great book below. There is also a video at the end of the page with a tutorial on the winning tips.
My Review of this Awesome New Book
Not all Excel books are created equal. My friend Bill Jelen (aka Mr. Excel) released a new book last month titled, MrExcel XL: The 40 Greatest Excel Tips of All Time.
This is Bill's 40th Excel book, and I have to say it is probably my favorite Excel book of all time.
This book is not only packed with Excel tips and tricks, it is also very entertaining and sure to provide some laughs.
Bill has put together the 40 Greatest Excel Tips Of All Time for this book, and it is packed with awesome time saving tips and techniques. If you have ever been to one of Bill's live seminars or read one of his other 39 books, then you've probably found yourself saying, “Wow! I never knew Excel could do that!” This new book is no exception.
The image above is an example of one of the tutorials in this book. I'm a visual learner and really appreciate all the screenshots.
30 Additional Excel Tutorials
Szilvia Juhasz (my friend and co-host of Excel TV) co-authored this book with Bill and added 30 additional Excel tips and tricks. This was in honor of Excel's 30th birthday this year, and this section of the book is packed with some fantastic tips.
Szilvia was also kind enough to mention my name in reference to my explanation of VLOOKUP with the Starbucks menu. Checkout page 194 for that one. Thank you Szilvia! 🙂
So that's 70 Excel tips and tutorials that are clearly explained with nice screenshots and images, all on glossy pages. It's an awesome book and I have picked up some great tips from it. No matter how long you have been using Excel, there is always something new to learn.
Have You Ever Had an Excel Cocktail?
But it doesn't stop there. This book is not only a great learning reference, it is also very entertaining.
Part 3 of the book includes Szilvia's new book called, The Excel Lover's Guide to Cocktails.
This is an Excel inspired cocktail recipe book. You will learn how to make some great drinks like the Power Pivotini (think Martini), Stacked Column Shot, Ctrl+B (think Bloody Mary), Broken Link (think Old Fashion), and many more.
The cocktail book is included as a section inside the book and contains some great pictures of each drink.
These would be a lot of fun to make for any office party, and I advise consuming after you have finished working with Excel for the day. 🙂
Get Ready to Laugh!
After you make yourself an Excel cocktail you can flip through part 4 of the book – Excel Fun.
This last section of the book includes an Excel Joke Book by Jordan Goldmeier, a collection of hilarious Excel Tweets collected by Debra Dalgleish, Excel Art by the Frankens Team, and a collection of True and Almost True Stories from Mr. Excel himself.
If you work with Excel a lot then you have experienced your fair share of ups and downs. Happy dances when things go right, and head pounding frustration when things break. This section of the book makes light of a lot of those challenges, and let's you know that you are not alone… 🙂
The inside back cover also includes the Periodic Table of Excel Keyboard Shortcuts by Mike Alexander. I love keyboard shortcuts and this is a really cool visual reference using the periodic table.
There is a ton of content packed into this 277 page book. I love to just flip through it and learn something new every time I pick it up. All the pages are glossy and you can tell that a lot of work went into the artwork and images. It's a great book to keep on your desk, or to give as a gift to your favorite Excel geek.
Win a Free Copy of the Book
To help spread the word about this awesome book, I am going to give away 4 free copies!
This will be the printed version of the the book. You can check it out here on Amazon
I will hold a drawing and to pick the winners at random.
I want to make it as easy as possible for you to enter the drawing. So, all you need to do is leave a comment at the bottom of this post with your favorite Excel tip.
This could be something as simple as your favorite keyboard shortcut or formula. One of my favorites is the Alt+; keyboard shortcut to select visible cells.
Add a sentence or two to describe what your shortcut or tip does, so others can learn from it. And don't worry about it being the most amazing tip ever, just something simple that saves you time.
Winners Announced!
I want to say a big THANK YOU to everyone that participated in this contest. There were over 350 unique comments, and a lot of great tips that we can all learn from.
Here are a list of the winners I picked at random. I accidentally picked 6, instead of 4, so we have two extra winners. Congratulations!
- Lynne – Ctrl+. (period) to move around selected range.
- Rajendra – Use Pivot Tables for working with numbers – Report Filter Pages
- Tom Crouch – Excel Tables and Ctrl+T
- Diane Smith – Double Clicking the Format Painter
- Matthew – F2 to Edit Text or Formula in active cell
- Debra Holcomb – Ctrl+; (semicolon) to enter today's date
Video of the 6 Winning Tips
I also put together a tutorial video of the 6 winning tips listed above.
I will be adding more videos and tutorials on the tips you posted in the future. Feel free to read through the comments below, and see how many of the tips you already know. 🙂
Thanks again!
Definitely VLOOKUP! It has saved me so much time at work, eliminating a lot of manual work!
One of my favorite formulas is “concatenate”. I am a CPA and often prepare taxes for clients with 800 or more stock sales in a year. I either use Able2Extract to turn PDF’s of their stock sale broker reports into an Excel File or get an Excel file from their broker. In any event, the number of shares and the name of the stock are usually still in separate columns and I need to have one cell with “200 shares XYZ stock” all together to import to my tax program. This awesome formula makes doing this a breeze!
text to columns is a favorite because it enables the ability to easily move data into separate columns for increased flexibility
I tried using VLOOKUP to search on a partial name and was having mixed results. Online I discovered an Add-in called Fuzzy Lookup and it allowed me to search on partial names with great results.
Mr Excel has helped me thru many frustrating Excel moments. ……one of my favorite helpful topics would have to be vlookup.
I work with many CSV files and files that contain lots of data in one cell with / as the separator. So to get my data into their own columns (text to columns) I couldn’t “EXCEL” without. Alt:A,E,N,O,/.
There are so many EXCELlent tips. My most used are the ctrl+c (cut) and ctrl+v (paste). I know that is 2 tips, but I like them equally.
Excel wanted to explore more and more
I love to use Vlookup mixed with Left or Right function when i need to search some especific data from a Cell
Transpose in paste special
You’ve got several rows. You want to change them to be columns – or vice versa. Select Paste Special, check the Transpose box, and paste with a different orientation
I often need to use the “text to columns” function to get a vlookup to work. If for some reason the columns being referenced in the two files are formatted differently, this always seems to do the trick.
“Insert Tab” .. “Screen Shot” .. “Screen Clipping”.
Copies selected cells to a dynamically updated picture that fits into any space. Double-click on the picture takes you to the source cells. Useful for Dashboard titles and provides a direct link back to the source work-sheet.
Correction: Tip steps should have been: Select Cells .. Right Click “Copy” .. Select target cell .. Right Click “Paste Special” .. “Linked Picture”.
This provides an adjustable picture link that when clicked takes you to the source data.
I used Vlookup quite often in the past to locate, search or match some specific information. while the Vlookup function does have some restrictions regarding the formats and sequence. So I found with the combined usage of “Index” and “Match” , many functions that can or can not be performed by the Vlookup are achieved.
I use Ctrl+Alt+V to paste special a lot.
Also, this book sounds awesome.
My favorite formula is the sumif()
Using AutoCorrect as a shortcut for commonly used words and phrases. Click File, Options, Proofing, AutoCorrect Options.
CTRL + Down arrow to go down to first non-blank row of the data range (Actually, any arrow key depending on the direction you want to go. Bonus, add shift and it selects)
That or double click on the fill button (It will fill down until the first blank row of the data next to it. ) – There are times it doesn’t work – mostly if you have already started filling or adding data in the column elsewhere – but still much better than dragging down.)
My can’t live without shortcut is:
CTRL+SHIFT+L to Turn on/ off filters
Hi Jon – love your site. I miss Andy and his adventures – the tips are well thought out, clear and simple (the way you present them). Thank you
I’m also in need of assistance, I was having some issues creating a UDF (User Defined Function). I am trying to ‘code’ account numbers (string or text values) to a code for Financial Statements. I was attempting to ‘code’ the Chart of Accounts (COA) to their categories on the statements – and I ran into a snag using the Select Case statement. I believe most of the issues were because it was a text field as opposed to a numeric field (in the Select case statement). I know in the past you had asked to email if we had questions, so here’s what I have (see above – the UDF or other method to accomplishe this). Thanks again SQ
The dashboard course reveals so many tips it’s hard to just pick one! I get so many poorly designed spreadsheets that I have to “clean” before I can use them, I like Paste Special Transpose – a lot!
I frequently use vlookup, those tips were indeed awesome
Press Ctrl+. to move the active cell from corner to corner of the range you have selected. That’s Ctrl plus period. The active cell moves from upper left (the default) to upper right, lower right, lower left, then back to upper left. This way you can verify your range without scrolling.
LOOKUP – either Vertical or Horizontal has saved me a lot of time and made my spreadsheets seems more advanced. I am self taught and was using basic formulas to do the same thing and it created a rather lengthy and unnecessary formulas. I LOVE EXCEL!!
Add your favorite options to Quick Access Toolbar (QAT) and then use alt+order e.g. alt+7 as short key to quickly access/initiate the command.
The F4 key functions as a redo (Ctrl+y)
Also double clicking the Format Painter allows you to format multiple cells without needs to use Ctrl or Shift.
SHIFT+END then UP or DOWN arrow to select a contiguous column of data.
The pivot table training was really good and I picked up a few time saving tips
Using =ISFORMULA() and conditional formatting when reviewing a new spreadsheet to tell which entries are formulas and which are hard coded data.
Took this approach from MyOnlineTrainingHub, when I use extracted data from an external source but then have to develop formulas to manipulate that data, I dot-fill in the cells with formulas as a visual clue to not key in these cells. This makes it easy for those that take over running the updates after me to understand where they should copy and what they should not overlay without being sure they want to overlay it. I do this consistently across all my work to make it easier for others to follow.
After formatting a cell, use CTRL+Y to format additional cells in the same manner. This works like the Format Painter except you can continue selecting different cells and not have to select the Format Painter each time.
To add or delete a row or column, use plus or minus:
Select the row/column
Ctrl and + to add
Ctrl and – to delete
Use it all the time, especially to add or delete a bunch of rows or columns quickly. You can continually hit the plus or minus, or select several or Ctrl and select which rows/columns you want to add or delete.
My favourite Excel tip:
Find&Select _ Go To Secial_ Blanks_ ‘=’ + cursor UP_ CTRL+ENTER
helps in situation when you have hunderd of blank cells after unmerging first column which contains x different items in second column. This tip fills in a second all blank cells with the original value as before unmerging.
Combination of index function and match function is a great idea to lookup value. If anyone get stuck at Lookup function (Vlookup, Hlookup, Lookup) Index and Match function will definitely easily help to ride off from the situation.
Favorite shortcut is Ctrl PgUP or Ctrl PgDn to scroll up or down the tabs in a workbook.
When creating formulas I like the F4 key. The F4 key cycles through all the combinations of absolute and relative references. I find it to be a time saver but mostly avoids the fiddly work of placing the “$” (the dollar signs) in the right place.
I like to use user form. so you can enter data in pop window, or link to another sheet. very convenience.
Copy the blank cells with previous filled cells: Highlight the whole column -> Ctrl+G->Special->Blank-> type “=” -> press key-board up arrow -> Ctrl+Enter. Then copy and paste value by : Ctrl+C->ALT ESV . Magic!
My all-time favorite Excel tip is filling in blanks with the Go To Special. This has saved me countless hours.
I absolutely love the ‘Flash Fill’ to either separate or merge information from one to two columns, or visa-versa.
I’ve become a huge fan of the today’s date shortcut (Ctrl + ;). Combined with the duplicate cell data above (Ctrl + ‘) it takes mere moments to enter repetitive data!
Replace Formulas with Values. I have a project that calls for replacing the formulas with values each time I update the spreadsheet. This tip helps. (1) Select the cells containing a formula, (2) Right-click on the border (4-way arrow), (3) Drag the selection to the new location and release, (4) A menu appears with the option “Copy here as values only”. Quick and easy!
Ctrl + ~ shows the formulas in the worksheet instead of the resulting values. You can toggle back and forth. It is good for checking for formula errors.
Excel Tip:
The N function (i.e. +N(“Hello”) – will leave a comment in a cell while still showing the value. The N function ignores Text
Wow, Michael, thank you for this one! I write some formulas that are more complex than the skills of some coworkers; now I can add an explanation without having cell comments. I love this!
It’s tricky to come up with only one favourite short-cut. I suppose for me the best time saving one would be Ctrl + # which applies the date format to the current select range. Used with the Ctrl + ; to insert the current date must have saved me hours over the years.
One of my new favorite Excel tips is the Flash Fill feature in 2013!
When using a table / range that has filters, when the active cell is in the filtered column header row, use ALT + DOWN ARROW + C to remove the filter that has been applied to the column
Moving the tasks I do the most to the quick access toolbar has saved me a ton of time, especially the things that are several menu items down. Things like Fill Justify and Center Across. I never can remember what menu they are in.
Cheryl,
Try creating your own tab…I did this and copied 95% of the icons that I use all of the time to it…one of the biggest time savers that I have come across – no more flipping between the Excel tabs to get to the one icon that I need.
Ken
Thanks, Ken! I haven’t played with that yet!
I love excel V lookup and Excel vba.
All shortcuts are important and useful in terms of creating efficiency. I’ve trained many recent college graduates in the fine art of Excel in real life application. If you asked any of them what my mantra is as it relates to Excel, unanimously they would tell you, “Less keystrokes, less time.”
Using sumproduct with some “ifs” built in, such as this example, where each =X is a variable.
=SUMPRODUCT(–(‘Baseline Revenue Projection’!$G$26:$AG$26=$E$8)*(‘Baseline Revenue Projection’!$C$27:$C$62=C14)*(‘Baseline Revenue Projection’!$D$27:$D$62=B14),’Baseline Revenue Projection’!$G$27:$AG$62).
A good example tutorial is here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html