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

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.

Online introduction to Excel: If you are just starting to use excel, this is the perfect resource for you. Here you will find dozens of audio courses that take a step by step approach to learning excel.
DataPig Technologies: The guys from Data Pig Technologies made a comprehensive collection of videos that explain almost every aspect of Excel. From basic Excel concepts to VBA programming. And most of the videos are free!
Online Charts Tutorial: Jon Peltier is an Excel-charting superstar. You can use his online tutorial to get you started on Excel charting and also as a reference.
Common uses for Formulas: This collection of samples will help you understand what can be achieved by using excel formulas.
Creating a Pivot table: A 7-minute video shows you how to create and work with Pivot Tables.
Pivot Tables in Excel 2007: Excel 2007 Pivot Tables are much easier to use. If you use excel 2007, check out this slightly promotional yet excellent introduction to Pivot Tables.
Practicing Pivot Tables: This step by step tutorial from Microsoft will help you sharpen your Pivot Table skills.
Microsoft Excel help / 2007: When all else fails, Microsoft Excel Help is a good source to try.

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.

Daily Dose of Excel: A blog managed by Dick Kusleika and authored by many Excel Experts and MVPs. “Daily Dose” is updated several times a week and profiles tips, tricks and news from the excel Industry. Highly recommended!
ExcelTip.com: Over the years ExcelTip amassed hundreds of tips and solutions to real world problems. You can either use the categories or the search function to find the information you want. You can also register to a tips newsletter. The site is managed by Joseph Rubin.
Official Microsoft 2007 Blog: The Official Excel Blog. Written by the Excel product managers/programmers. It contains a lot of information and how-to articles about Excel. The level of articles on this blog varies from “useful to everyone” to “only for hardcore excel services programmers.”
Vital News Excel Tips: Very similar to ExcelTip, this site contains hundreds of tips sorted into categories and offers a weekly newsletter.
Chip Pearson Newsletter: A new service from Chip Pearson. Each issue of this newsletter contains a thorough examination of a technique or of an Excel function. For those of you that wish to gain deep knowledge of Excel, this is an excellent resource.
Excel User: Excel User contains a “Visitor Question” section and many high quality articles. This site was created and maintained by Charles Kyd.
Andrew’s Excel Tips: Andrew Engwirda writes one of the best Excel blogs. What separates Andrew from the flock is his vast amount of Excel and programming experience.
Codswallop: Although we are not a strictly an Excel-centered blog, we plan to bring a lot of Excel goodies in near future. Don’t hesitate … subscribe now!
Smurf On Spreadsheets: Simon Murphy is an Excel programming master. If you are into Excel Programming, this blog is perfect for you.
The Ken Puls Blog: Ken’s blog is neither Excel centered nor frequently updated. However, when Ken does blog about Excel, it’s pure gold. Definitely worth checking out.
XL Dennis: Dennis Wallentin writes about developing Excel centered solutions with the .Net framework.
Excel Hero: Daniel Ferry’s blog is full of Excel tips and advanced topics intended to inspire you and make you the Excel Hero at your workplace.

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.