The Excel Magician: 60+ Excel Tips and Shortcuts to help you make Excel Magic

Posted by nitzan on Wednesday, November 28th, 2007

Are you working with Excel and want take your Excel skills to the next level? Or do you want to learn Excel and don’t know where to start? Check out these 70+ tips and shortcuts that will help you make Excel Magic.

Online tutorials & videos

The following online tutorials are mostly free and will teach you quite a bit about Excel. In fact they are better than some of the expensive classroom training courses.

Books

In order to harness the full power of Excel, shell out a couple of book bucks. The following books are packed with information and real-world know-how.

General Excel Books

Excel Bible 2003 / 2007 version: The “Excel Bible” was written by the renowned Excel expert, John Walkenbach. It explains everything from basic formulas and functions to data validation, and Excel programming. If you have only $30 to spend on Excel training, buy this book. Excel Charts: This book is a comprehensive, yet easy to understand, guide to Excel charting. It’s a useful resource for both beginner and experienced excel users. Excel Formulas: Formulas are the lifeblood of spreadsheets and “Excel Formulas” from John Walkenbach will teach you everything about them. This book covers all things formula, from custom worksheet functions to financials formulas and more. Pivot Tables and data analysis / 2007 version: One of the most useful yet most feared features in Excel… the Pivot Table, is tackled gracefully by Bill Jelen (aka Mr. Excel) and Michael Alexander. Well worth the read. Excel Programming: By far, the best guide to Excel programming. The book also outlines a programming methodology for Excel. The only downside to this book is that it assumes a bit of programming knowledge. Report programming with Excel: If you plan to build a reporting system based on excel, this is the book for you. It shows how to use Excel to build a reporting/data analysis environment and shows how to properly work with SQL databases.

Excel Tips and case studies

Excel case studies: While not for the beginner, this book contains valuable, real-world advice on how to make Fxcel do what you want it to do. Make sure you check out the “Making things look good” chapter. Excel Tips: A highly recommended Excel tip book from Mr. Spreadsheet himself. Some more Excel Tips: A compendium of Excel tips. This is not the first book you should own, but I often find that I return to this book when I’m stuck. This isn’t Excel it’s Magic: Bob Umlas is probably the foremost expert on formulas. The things this guy does with formulas will make your hair stand on end. If you are serious about Excel, than buy this book.

Specialized Excel books

Principals of finance with Excel: This highly recommended book will help you understand the applicability of Excel in financial environments. It is loaded with real world examples and can help both the financial expert and the techie. Statistical Analysis with Excel: Using plain English and real-life examples, this book provides information that helps with statistical analysis. The book covers samples and normal distributions, probabilities and related distributions, trends and correlations, as well as statistical terms like median vs. mean, margin of error, standard deviation, permutations, and correlations. Business Analysis with Excel: Running a business is complicated. Understanding issues like cost of goods, inventory, sales forecast, tax statements is crucial to success. Business analysis with Excel explains these issues and shows how to tackle them using Excel. Sales Forecasting with Excel: This book shows you how to use Microsoft Excel, to predict trends and future sales based on… numbers. Use data about the past to forecast the future. Excel provides all sorts of tools to help you do that, and this book shows you how to use them. Excel for Chemists: While most of this book is a general introduction to Excel, it is filled with Chemistry oriented examples. The book also contains a complete chapter that shows how Excel can assist chemists in research.

Forums, News Groups and Mailing Lists

No matter how tough or silly your question is, the experts in the following sites/mailing lists will answer it. They will do it for free and usually within a couple of hours. Don’t be shy. Join these communities and ask.

Note: The online Excel community is one of the nicest communities that I have ever had the pleasure of joining.

Mailing Lists: Wow. This is the jackpot. The Excel-G mailing list is monitored by the best Excel experts in the world. They answer every question. If you post an interesting enough problem these Excel gurus will compete among themselves to answer first and give the most elegant solution. ExcelForum.com: ExcelForum.com provides a web interface to the Excel News groups. If you do not want to be bothered with the USENET interface, this site will is a useful alternative.

Excel Experts

Some Excel projects are too big/difficult to tackle alone. Here is a (short) list of some of the best hired guns in the Excel Field (If you know other top-notch Excel experts, drop a link to their site in the comments).

Jon Peltier: If you have a charting project/problem, I would recommend working with Jon. Jon brings to the table over 20 years of Excel experience A PhD from MIT and is a Microsoft Excel MVP. Chip Pearson: Mr. Pearson is a renowned Excel expert and while his fees are not low, he is one of the best. If you need an urgent solution or have a critical project, I would consider asking Chip for help. Mr. Excel Consulting Services: The Mr. Excel team is probably the largest Excel consultancy in the world. Their ranks include numerous excel MVPs and they have an amazing amount of Excel Knowledge. JMT Consulting: A consulting service from two respected Excel MVPs: Masaru Kaji and Andrew Engwirda.

Excel Blogs and Tip Sites

Tips sites and Excel blogs will usually send you a daily Excel tip. Many Excel professionals register to these sites and read the daily tips to keep their Excel skills sharp. They also serve as repositories for thousands of Excel case studies.

Excel Templates

Templates can be a huge time saver and odds are that the spreadsheet you are trying to build already exists. We’ve divided Microsoft’s huge template repository into useful categories so you’ll be able to find the right template for you.

Business Related Templates

Budget Templates: Whether you are managing your personal budget, your Wedding budget or your gardening budget, you’ll find a template for it here. Balance Sheets: You can find almost any kind of balance sheet here. Expense Reports: Unexpected expenses can have a nasty effect on your bottom line. Use these templates to record and control expenses (including traveling expenses). Business Forms: Here you can find all types of different forms, from a traveling advance request form to a car mileage log. Inventory Templates: Manage and track you inventory with these templates. Invoices, Work Orders, Packing Slips: This is a real time saver. Whether you work in retail or services, you will find the right invoice/work order template here. Purchase Orders: Not only will these templates help you get the exact the items you need on time and delivered to the right place, they also come in a variety of colors. Receipts: A variety of receipt templates. Time Sheets: Use these templates to track employee work time. You can choose a template that will sum the employee and overall working hours on a daily, weekly or even monthly basis. All kinds of Reports: Different financial and management reports.

Other Templates

All Kinds of Lists: Phone List, Grocery List, Reading List, Gift List and much more. Planning Templates: Business and personal planning templates. Schedules: Schedule templates for your employee shifts, business and personal events.

AdditionalTemplate Sites

Vertex42 Excel Templates: Dozens of Excel templates. Some even come with a user manual. OZGrid Excel Templates Page: Another big and famous collection of templates.

Excel Tools

Excel is the ultimate killer app. But there are cases where even Excel needs a little help. Here are some Excel Add-ins that can double your effectivness.

Asap Utilities: Probably the best known Excel productivity add-in. Asap utilities contains advance selection options, advanced browsing capabilities, better formula handling and much more. Excel Sentry: Use the Excel Sentry to prevent your business data from falling into the competition’s hands. The Excel sentry allows you to encrypt your spreadsheet in such a way that only you or your employees/coworkers can use it. XL Statistics: A free statistics package that expands the existing Excel functionality. PDF to Excel: One of the most stubborn sources of data for Excel is PDF files. Whether they are scanned or not, PDF2XL will extract the data for you. FlorenceSoft: This cool little app allows you to easily find the differences between two different sheets. Excel Password Remover: Do you have a terribly important sheet you encrypted and then forgot the password? The Excel Password remover is your locksmith. Tree Plan: A set of data analysis tools from Mike Middleton. DPlot: Create 2D and 3D graphs and plots with DPlot. Especially suited for Engineers and scientist that need expanded charting and plotting functionality. DPlot contains unique chart types such as, the Polar Chart, The triangle plot and more. DigDB: Another well known Microsoft Excel productivity add-in.

Additional Excel Resources

Excel User Conference: The Excel user Conference, run by Daemon Longworth (MVP), is by far the best venue to advance you Excel skills. You will learn high-end Excel tips and tricks from the best Excel experts (all the instructors are Microsoft MVPs). Plus, everyone is extremely friendly and you’ll get a bunch of laughs and even a couple of beers. Excel funny videos: Who said Excel wasn’t fun? Excel games: And to top the list. I present Excel… the gaming platform.

Now it’s time to Excel.