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!
Excel tips:-
Alt+D+F+F = to add/remove filters.
CTRL F9 in a VLOOKUP over the Table_Array to move the Lookup values into the Formula as text. (Only for short lists).
My favorite tip is to use F9 after selecting a part of formula. This helps me to evaluate things step by step, even when “evaluate formula” feature fails.
Favorite excel tip is hiding cell contents by changing number format to ;;;
Vlookup explanation tip
Great initiative Jon. Here is my Excel tip:
I love a keyboard shortcut that moves around the 4 corners of a range. Just select a cell within a range, press and hold CTRL key in your keyboard and then keep pressing Period operator in the keyboard. The active cell will move around the 4 corners of the range.
Now that is cool! Wonder what I could use it for…
When you are in a big range and you don’t know how long the range is, you can use this keyboard shortcut to know the length and width of the range.
Perhaps not my favorite, but it’s the one I use most and that saves me a heep of time every day. To insert a column use ctrl+spacebar to select the entire column, then ctr+(numpad +) to insert column. Use ctr+(numpad -) to delete. The same applies to rows, just use shift+spacebar instead.
Use Tables.. Not Ranges! Converting your data range to a table is a no-brainer. Tables not only come with built-in filters, totals, and cool conditional formatting, but they become so versatile when referencing them, especially from VBA. Tables sort out the chaos of inconsistent, mediocre data and transform it so it is far easier to manage and reference. I tables.
I agree! I just wish they didn’t make my files so huge!
It have to be macros. At my student job at the university, I work with a lot of raw data. Macros is very effective when it is the same progess to compile the data.
I rarely won a lucky draw. But I would like to give it a try! 🙂
Here’s a tip to get rid of GETPIVOTDATA without turning it off.
http://wmfexcel.com/2015/10/24/get-rid-of-the-getpivotdata-without-disabling-it/
Hope you like it.
Hi Jon, just curious… would my chance of winning increase if I leave more comments?
Cheers,
MF 😛
Hi MF, Please only one comment per person. Thanks!
Ctrl+Shift+End
Select all data.
Now that I am working with 3-4 linked workbooks, the control+’ (ctrl + tilde key -leftmost key on the number row) to ensure that links on my worksheet come from only one particular workbook is very helpful.
The BUDGET in my company is being updated often so it’s always good to ensure that the values are being pulled from the latest budget and not a mixture of old and new budget – leading to incorrect numbers.
I really love the ctrl+shift shortcut to insert today’s date in a cell!
In excel, if you change the zoom level % to less than 40% it will show where the (static) named ranges are on the sheet.
CTRL F9 in a VLOOKUP
nice tip
Simple but extremely useful:
Enter date by using CTRL+ ;
Still learning, so can’t say which is favorite. However, would love to check out your book.
I have a lot of favorite Excel tips, but probably one of my favorites is this:
When you’re trying to copy and paste Pivot Table data to a new area (as values), but you want all of the blank spaces filled in, select the area that you want filled in, hit F5, click Go To, then select blanks. Once you have them selected, type = then hit the up arrow key (to select the cell above the current cell). Once that’s in the cell, do Ctrl+Shift+Enter, which will apply it to all of the blanks. Saves a LOT of time! 🙂
Great idea this! Just read a bunch of tips I didn’t know.
VLookUps have recently saved me hours and hours of work!
Alt + H F P
Activates Format Painter. Once Ctrl1 has been used to apply the specific formatting of characters styles and cell formatting, Format Painter is my friend from there on out. Tremendous time saver, plus it looks great using the short cut keys instead of the mouse when friends/coworkers are watching.
My tip:
Save yourself a lot of copy&paste work if you have occurring lines: use Fill 🙂
looks a great book – pick me… 🙂
Using icons with conditional formatting. Great for dashboards.
Paste special – Transpose is a fantastically simple way of turning data round from lines to columns or vice versa to make it easier to read and work with, especially with long field values such as text (addresses, names, designation etc).
Control + Z to undo and Control + Y to respectively undo and re-do must be two of the best shortcuts in the world (to err is human…)
Thank you so much for you help these past two weeks. The pivot table exercises are fantastic and your videos are excellent.
Thanks again Jon
The *Best* Excel keyboard shortcut is CMD+1 (Mac) or CTRL+1 (Windows). Get you right to the format menu, for anything–cells, graphs, whatever.
Thanks.
Double-clicking the Format “Paint Brush” to format multiple lines or columns
My favorite tip is autofill.
When I was younger, my roommate worked at Microsoft and programmed the very first version of autofill. I forgot about it until this tip appeared.
Mine’s a simple but good one… ctrl+’… which enters the value from the cell above
Paste Special to Transpose
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 absolutely love the F4 short form key for the $ to ensure that a cells information stays consistent. This has been very helpful since I use spreadsheet all the time.
I actually got a copy of this book already – I helped fund his IndieGoGo project for this. Just wanted to leave a comment to let you know how much I enjoyed it. Some really great tips (I didn’t know you could get distinct count in Excel 2010. That has been a HUGE time saver!) No need to enter me in the drawing 🙂
F2 – to edit a cell
My favourite excel tips:
There are many. But my most favourite would be using INDEX and MATCH for lookups.
Double-click on the lines between columns to condense the column to the minimum width needed to show the longest entry.
I thought I knew a lot about excel until I stumbled upon the shortcuts to add the date and time without a lot of work
CTL + ; for the date
CTL + SHIFT + ; for the time
now I keep wondering why Microsoft hasn’t put these into Outlook and Word!
Disable resizing and moving of pivot table slicers – right click on the slicer, choose size and properties, select position and layout, and then disable resizing and moving.
I really want this book!
Using “min of” and “max of” in a pivot table value field setting.
Say, for instance, you have a list of document numbers, and each document has a list of line items. The creation date is by line item, and you want to know when the document was created (earliest creation date). IN the pivot table, put the Creation date in the Value field and change the value field setting to Min. That will get the earliest creation date. The same would hold true for a completion date, but change it to Max (of completion date).
One of the things I use most often in Excel is to substitute “alt+=” for a sum at the bottom or left of a set of values. Makes summing so easy and you don’t have to take your hands off the keyboard!
My favorite tips are related to VLOOKUP function 🙂
Excellent tricks and tips with EXCEL, lessons and webinars are straightforward with added details to navigate and set up or duplicate in own spreadsheet..thanks for sharing your talents.
I think the slicer function has become my favorite new feature. If you have not tried it with your pivot table you should check it out!
I love Drop Down Lists…or Data Validation. It’s super easy and if you build forms or reports it can make them more interactive and reduce the chances of the wrong data being entered.
Some of my favorites:
CTRL+SHIFT+Right Arrow – select all data to the right
CTRL+SHIFT+Down Arrow – Select all data down
CTRL+Down Attow – Advance down to the last cell with data
the vlook explanation was great it can be a hard concept to grasp but it was done clearly
VLOOKUP Function! I am forever comparing lists, and the ability to have unique values pop out for me has saved me endless hours. (I even managed to figure it out without the Starbucks example, but that example means I remember it instead of having to look it up every time!)
Excel tip –
Ctrl + C to copy data
I use this everyday; it saves me a lot of time!
When merging rows of header for converting Data sheets ready for Pivot I press CTRL+G for selecting all blank spaces and press CTRL+ENTER…And all merged header are converted to Pivot ready.
I listened to the interview with Mr Excel. It was very informative and even though I have been working with Excel for 20 years I learned many new tips. I am looking forward to reading his new book.
Copy data and paste directly into a Chart! Great tip
I love quite a few of those already mentioned. Excel has so many wonderful features and shortcuts