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.
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. - Basic Formulas Guide: This excellent tutorial will help you master Excel formulas in no time.
- Common uses for Formulas: This collection of samples will help you understand what can be achieved by using excel formulas.
- An introduction to Pivot Tables:
The Pivot Table is an amazing tool, but people often shy away from it
because Pivot Tables seem complicated. The first page of this PDF
contains a clear description of Pivot Tables and how they can be used. - 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. - Mr. Excel Message Boards:
A very friendly forum whose members will usually provide you with an
answer within 3-5 hours. A bunch of Microsoft MVPs (including the Mr.
Excel gang) monitor the forums. And, of course, it has a pure html
interface which makes it easier to use. - Excel News Groups:
If you prefer USENET groups to mailing lists or Message boards, than
you’ll love the Microsoft Excel groups. Most questions asked will be
answered within 12 hours. - 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 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. - Send Mail:
This cool little freebie from Ron de Bruin that allows you to send an
email with the contents of a workbook, a single sheet or even a
selection area. - 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.
- Palo:
A free (open source) OLAP server for excel. On-line Analytical
Processing servers usually cost hundreds of thousands and sometimes
even millions of dollars. Jedox (the company that made Palo) is giving
it away. Definitely worth checking out. - 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. - Charts by Jorge Camoes: A site dedicated to Excel charts and charts add-ins. Also runs a chart centered blog.
- 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.
