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!
Thank you!…
For now, my favorite is using Excel is the Ctrl+ Shift + arrow key to highlight a whole column.
Ctrl+’ to enter the same contents of immediate cell above.
Make text in a cell disappear.
1) ctrl 1
2) go to custom
3) type ;;;
Bingo your text is not visible to anyone.
Thank You……
Bhavani Seetal Lal
9160161764.
[email protected]
Always put comments lines into your VBA code, to explain each step.
It makes it so much easier for others to follow, and the real bonus is, you can follow your own thinking process, months (or years) later.
If someone is using slicer you can connect more graphs with just a one slicer.
Right click on slicer (report connection) and you have it. Simply
Interactive Excel Dashboards tips
I always save a lot of time when I try to mark large ranges in a worksheet if I use instead of the mouse the keyboard short cuts
Ctrl+Shift+Right Arrow – to mark the row to the right from the selected cell
or
Ctrl+Shift+Down Arrow – to mark the column down from the selected cell
or
Ctrl+”*” – to mark the range around the selected cell
But the best invention since Pivot Tables is the introduction of Power Query to me!
I am really excited about all the features that Excel offers and how I will be able to them to my everyday job.
Ctrl+` to show all the formulas in the active sheet.
It’s awesome when you don’t have a well formatted table from someone.
I love the tip for selecting all visible cells. Not only the shortcut, (I’m the shortcut queen) but the tip itself. This is how I found this awesome blog and signed up for the newsletter.
ALT+ENTER give you better understandig formula
Double clicking Format Painter to continually copy a format.
What
a
lifesaver.
shift+Control+up/down/home/end – saves so much time!
shift+Control+up/down/home/end keyborad action
save my time good tips
There are so many but I like Cntl ~ to see all formulas. I also like double clicking format painter to keep the format painter active for use in multiple cells.
Control+SHIFT+[cursor] down arrow. Select rows and columns easily for copy, cut, sort.
shift+Control+up/down/home/end save my time
Using a startup file with pictures and hyperlinks to my most used files, programs, web pages. I’ts cleaner than my desktop and easily organized.
F2 to edit the existing text in a cell…nice keyboard stroke so you don’t have to double click into the cell or into the formula bar
I’d have to say my favorite tip has been the customizing of the QAT bar – now if I could just convince Excel to let me load a different Quick Access Toolbar for each type of file I’m working in, I’d be in heaven.
As a work around to that – I’ve built three different custom tabs in the ribbon – and I just select the one I need for the type of file I’m working in.
I must love the varying function manipulations. Let’s not forget the advanced pivot uses. Has helped me a great deal with manipulation of the data I need to evaluate at any given time.
my favorite tip is filling in blanks in a column.
Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option. Click OK, and all of the blank cells have been selected. Then input the formula “=A2” into active cell A3 without changing the selection. Then Ctrl + Enter.
for large workbooks with alot of tabs, using open new window and view side by side to view multiple tabs in the same workbook and switch between them
my favorite shortcut is Ctrl-Z…. awesome.
Deleting blank rows
select a region, Ctrl + G (select special) then select blanks
then delete entire row
Ctrl+Enter to 1) stay in the same cell I’m editing, and 2) to enter the same thing into a bunch of cells all at once. When I learned this trick a couple years ago I just had to do a happy dance, and even today, every time I use it my heart flutters with joy!
CTRL+ENTER array formula
My favorite is using an array to sum ranges from various places in a workbook.
Most time saving for me is have a Customized Quick Access Toolbar. This allows those Excel commands I use the most often to be readily available. I keep an Excel Workbook with the list of functions, so I get a new computer or and upgrade, I first thing this the Quick Access Toolbar ready to use. (For example, I can pop into VBA with click on button, rather than having to hit Alt-F11)
Hi JON,
You simplify things so they stick in my mind.
I’ve shared your site with an office mate and we teach each other the shortcuts we learn.
Thank You for saving me time and making me job more enjoyable.
Cathy
Clicking on square in bottom right corner of active cell and dragging down to copy to other cells.
Countifs() Great way to breakdown multiple conditions listed in tables.
I have to make a paper job-sheet logs for field techs, and have a lot of info to cram in, and still leave space for them to comment.
Concatenate(info 1,” [alt+enter] ”
, info 2)
by pressing alt+enter in the formula it will (should!) make double lines (may need to format to “wrap text”). hide columns that had the info
Great tip!
I have read most of the comments and your tip is probably the only one I didn’t know before. So cool! I used to do the same thing with the CHAR(10) function 🙂
Alt + = to automatically sum a column
Apple key+Shift+T saves a lot of time in calculation
By double clicking format paint brush, you can copy the format to non-adjacent cells
When I’ll need multiple chart views from a pivot table for a hard copy report, (yes, some people still require them) I copy and paste the pivot table and drag and drop the row items from my field list. There’s no reformatting of the column or value fields. Keeping it all on one worksheet makes it easy to relocate the exact data source.
For me, CTRL + D, is a shortcut to good use in excel. The Ctrl + R has the same importance.
I’m a fan of the function keys so I love Alt + F5 (Refresh) and
Ctrl + A (selects whole range). These are huge time savers.
Thanks!!!
My favourite short cut is Alt = which adds many numbers very quickly
I like converting data into tables prior to establishing the pivot table. Enables higher levels of control for the source data and simplifies the selection of data input for the pivot table.
Ctrl + * to highlight the whole table!
Ctrl+u helps inexpanding the adrees bar and alt +h+o+w helps in resizing the column.
Hi!!
My favourite tip of Excel is the way of search data with VLOOKUP to the left with CHOOSE function.
Thank you.
Dynamic charts, drop-down lists, etc based on tables
CTRL+HOME
CTRL+arrow key
Keyboard shortcuts to move quickly across a table
CTRL+SHIFT+arrow key
Selecting cells quickly across a table
Selecting worksheets with Ctrl + Shift + Page Down
Data Validation what a learning experience. Best of, from a known user from Mr Excel. Amazing site and learning curve from these postings.
I have learned so many awesome tips, but one of my favourites is using the autofioter>search on a column of a large spreadsheet when autofilter is turned on.
I loved learning how to create interactive dashboards by watching your three video tutorial.
For charting, to copy formatting. Copy, paste special, formats. Saves so much time to not have to reformat each chart built.