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!
Here is a great tip. Add the screen capture icon to the QAT in the same position in all the Microsoft Office apps. This way you can use the same keyboard shortcut to create a basic screen capture no matter the Office App you have open.
My favorite tip is after highlighting a range of data, you can right click on the right edge of the highlighting and drag it one column right and then back to it’s original location (without letting go the right click). A menu appears that allows you to paste the values of the cells.
favorite excel tips are concatenate , right and left
VLOOKUP is one of my favorites! Also CTR + Shift + arrows (down; left) to select the data.
Dear, Great Campus
I know a new shortcut to filter some columns
just press {Alt then A then T}, its a new way to turn filter on/off, its also three buttons to click and you can try it with one hand
I hope it could be more easy or useful .
I really, really, want to win this book. My favorite thing in excel is the power of changing lists into tables and then be able to pivot that data anyway I want or need it. VLOOKUPs are a lifesaver also.
I find + as well as + very useful to select entire columns or rows quickly!
Excel tips –
Paste Special – Transpose
Vlook up
Awesome way to explain it.
Hey Jon, great job & hoping to get win…..
The trick that I use the most since learning it has to be Ctrl +1 in order to bring up the format cells menu.
CTRL Enter: When a range is selected we can fill it using the first cell and pressing CTRL Enter to paste the result across the range. Useful to enter same data in scattered blank cells if used in conjunction with “Go to –> Blanks” before that.
– When CTRL Enter is pressed in a single cell the cursor does not move to the next one (The default option in Excel is for the cursor to move one cell down when Enter is pressed)
My favorite Excel tip would be advanced vlookup – if you use Indirect function where you can convert any named range to a reference. You can extract very tricky information for a dashboard !!
Ctrl-PageUp and Ctrl-PageDown for stepping through worksheets in a workbook (even when show tabs is set to off).
I LOVE the ALT + = shortcut for auto sum.. Learned about it yesterday from your site and used it 4 times today (so far) !!
My favorite tip is using ALT F11 to access Visual Basic to edit Macros in my Personal Macro Workbook. For years, I would unhide my PERSONAL workbook to edit my macros, then have to remember to rehide it.
Ctrl (+ or -) then click (r or c) to delete rows or columns..
Here’s a tip I just found on the web. Have to attribute to “firewatch”. I needed to number the duplicates and then generate a column I could use to identify the 1st, 2nd, 3rd duplicate. So as long as your data is sorted (col B), to number the duplicates of col B: =IF(B2=B1,C1+1, 1).
You can then concatenate col B and Col C to get a unique identifier for each duplicate by 1st dup, 2nd dup etc.
See below for a practical application.
I had a spreadsheet of names with phone and address info repeated for each parent and I wanted to just get a list of all the families with each parents name and all the sibling names. In this example, I just use 123 etc for phone number and assume phone number is unique to family.
Name Phone Dups phone + dups parent
Sally 123 1 123-1 Mom 1
Sally 123 2 123-2 Dad 1
Joe 123 3 123-3 Mom 1
Joe 123 4 123-4 Dad 1
Jose 234 1 234-1 Mom 2
Jose 234 2 234-2 Dad 2
Carla 345 1 345-1 Mom 3
Susie 567 1 567-1 Mom 4
Susie 567 2 567-2 Dad 4
so I wanted the data to be:
Sally 123 1 Mom 1 Dad 1 Joe
Jose 234 1 Mom 2 Dad 2 #N/A
Carla 345 1 Mom 3 #N/A #N/A
Susie 567 1 Mom 4 Dad 4 #N/A
So you can use index and match to get the parents names and the sib names.
My favorite excel shortcuts right now are the filter shortcuts (Alt+down arrow). I am practicing them and using them to do all of my data crunching with Excel tables right now.
This shortcut is simple but I use it constantly, F12 to “Save As”.
My favorite tip is after highlighting a range of data, you can right click on the right edge of the highlighting and drag it one column right and then back to it’s original location (without letting go the right click). A menu appears that allows you to paste the values of the cells.
If I have to pick two of my favorite shortcuts, Ctrl + ~ and Ctrl + * are the ones that I almost use everyday.
Creating a shortcut key to enter today’s date. It save lots of data entry time.
my favorite tip is to copy the formula in cells below by clicking the + sign in the cell with formula. comes in handy if i have several thousand cells to fill
I love all the short cut keys functions that save me ton of times !
Alt + =
F2 & Escape keys
If functions
I use VLOOKUP for everything and anything 🙂
Still not very good in INDEX function though..
Love this course. Highly recommended.
My favourite tip is the Camera tool!!! It’s the best!!!
I like to use sumproduct where I would have used countif or countifs. SUMPRODUCT can be used to consider many more factors at once, and can also use logic to determine which values to include. I have used this method to count or total items from lists with criteria referencing the values in multiple columns.
Alt+O, C, A
Makes columns the width of selected cells
Using Index Match has helped with a lot of files where vlookups wouldn’t work. I am able to pull data based on column heading name rather than having to count the number of columns. I can also refer to columns other than the first one in the data. I’ve told several co-workers to use index-match rather than vlookups to help save time. They are always surprised that at how flexible it is.
Thank you
I just love Excel & Jon makes learning so much easier & fun
Use “&” to quickly concatenate info in two (or more) cells.
I.e.
=A1&A2, or
=A1&A2&A3
Or use CONCATENATE itself to get more fancy in adding in spaces or other values in between the cell contents
Concatenate with a space:
=CONCATENATE(A1,” “,A2)
You can also accomplish that with the “&” function but it’s a little more convoluted to look at when you get more than two items in there:
=A1&” “&A2&” “&A3
My favorite Excel trick is combine INDEX with MATCH. You can do wonderful things with the help of those two. 🙂
I’m torn – trace precedents & show formulae help, but then I found out about Data/Sort & Filter/Advanced/Unique records only, and that is a new weapon in my Excel arsenal! Happy to learn more!
I’m into Pivot Tables lately. Loved your tips on cleaning the source data before creating the table (eliminating blank rows and blanks in key cells, and making sure the formatting of the data was appropriate).
Creating Named Ranges. I love name ranges because they make formulas so much easier and I can look at the formula and understand what data is being used verses having to go look at that cell or range.
Using ALT + # or Letter to switch tabs, save, etc. Shortcut keys…
Don’t merge cells to center a header above multiple columns. Instead, use “center across selection” alignment.
My favourite short cut in Excel are Ctrl + C (for copying) and Ctrl + V (to paste). I also love the paste special function. I also love Vlookup for finding data and corresponding figures.
Hi,
Please select SHIFT+CTRL+”-> or other arrows to select all items
In column A & Column B, till 100 rows I have values to sum up in column C.
So I write a formula in C2 as =sum(A1,A2) and it sums the result. I can not write a formula in each and every row manually. So I use Ctr+D to fill the same formula in the remaining cells or I use Mouse double click short which drags down the same formula to the remaining 99 rows.
Since Excel does not deal well with time – I learned to use Random decimals that correspond to various times of the day.
My tip:
To obtain the last day of the preceding month:
=DATE(YEAR(A1),MONTH(A1),0)
My favorite tip right now is shading lines based on the value in a column. I use =AND(MOD($A1,2)=0,$A1″”) to make sure that all the even numbered lines are shaded in my order form. It makes it so much easier to separate the orders
My favorite Excel shortcut is CTRL T for tables. You automatically get filters, freeze panes for the column headers and shading on alternate rows to improve readability. Tables do much more but I mainly use them for the formatting.
I never used tables, but once I found out about them and how they work, well… now every is a table.
But it wasn’t until I learned about ctrl-T that they became mainstream for me! That and a ctrl-L double tap to quickly clear all filters on the table!
Oh, oh, oh – and INDEX(MATCH) – use that EVERYWHERE
Too. Many. Favourites! (forgive the spelling – I spelled it the right way (I’m Canadian Eh!))
One of my favorite tricka is the INDEX-MATCH combination to make a VLOOKUP to the left!!! This trick has solved many situations for me!!!!
My favorite Excel tip is using VBA/Data tables to create awesome reports! I want to continue to improve my Excel knowledge. I know with your book, I can become an expert like you!
I write a lot of VBA Code and have been recently learning about Improving VBA Efficiency. One of my favorite tricks is to limit the range by either using the Intersect Function or by using the Range.SpecialCells Method.
My favourite, dead easy, tip for more effective use of Excel is Tables. They make everything easy, including filter buttons added by default, the ability to use the table and column names in formulas, but probably most useful are the auto expand of the table, including formulas, when adding new data at the bottom and also the auto calculated columns feature. The time saved in copying and pasting formulas is incredible. Go on try it today, Insert Table!
Power Query!!
My favourite tip is a keyboard shortcut:
End+Shift+Enter – Extends selection to the last non-empty cell in the row
My favorite trick is the INDEX+MATCH formulas combination. This lets make a VLOOKUP to the left!!!. This has helped me many times!!!!
VLOOKUP was my favorite new command, but now I’m in love with slicers. Loved your video showing me how they are so easy!
my favourite shortcut is ctrl +t to insert table.
table is much more flexible to work table.