How-to Microsoft Excel Guides

These how-to articles appeared in our free, twice-monthly e-mail newsletter. To receive your own copies, please add your name to the list.

Seven Excel Tips for 2019.
Everyone likes to get a fresh start for the New Year. Here are a few simple things you can do in Excel that will make you more productive. Read Seven Excel Tips.

Using Named Ranges in Excel.
Shakespeare tells us that a rose by any other name would smell as sweet. But think how confusing it would be if every florist had a different name for the same flower — it’s nice to know that a rose is a rose everywhere.

Likewise Excel becomes a lot easier to use if we give names to cell ranges we frequently work with. Then we can reference them with easy to remember names like Revenue, Sales and Expenses rather than cell ranges like A1:A100. Learn How to Create and Use Named Ranges.

Highlight Key Metrics with Excel Conditional Formatting.
Visual cues help us grasp situations at a glance. For example, we rely on colored lights when driving to tell us whether to stop, go or pull over. We could never read conditions as quickly without them. The same is true with large data sets. That’s what makes Excel’s conditional formatting so useful — it lets us create easy-to-read visual cues to highlight key items. Read How Conditional Formatting Can Make Your Spreadsheets Easier to Read.

Excel’s IF Function.
Switches let a railway dispatcher route a train over one of several tracks. Excel users similarly sometimes need to make calculations that apply one equation in some circumstances and a different one in others. For example, a bonus may be computed differently for long-time employees than for others. That type of calculation is sometimes said to have a conditional result and the Excel’s IF function is the tool for making them. Get all the details in how to make them in IF and Other Logical Functions.

Using the VLOOKUP Function.
Excel’s VLOOKUP function is like having a reference librarian in your spreadsheet. It takes an input value, tries to find it in a reference table and, if it does, gives you related data from the table. For example, it can lookup the name of a client associated with a given account number. Read How to Use the VLOOKUP Function.

Using the Mouse to Edit in Excel.
Does Excel sometimes seem to have a mind of its own? You want to highlight cells and all of a sudden their contents move. The secret is to know that there are three different icons that can appear as you move your mouse over a worksheet. Once you understand what each means and how to get the one you want, you are in control. Get our tips for Editing Your Spreadsheets with the Mouse.

Organizing Data for Analysis.
You’ve gathered your data and put it all in the computer. You open your database or spreadsheet anxious to see what secrets it reveals. Then frustration — no matter what you do you can’t produce the analysis you want. Why, everything that’s needed seems to be there? Learn How to Structure Data for Analysis.

Getting It Right (Avoiding and Finding Errors).
Excel is great for crunching numbers and analyzing data. It does exactly what you say and won’t complain if there is a lot of work. All is good so long as you correctly explain what you need. If you don’t, you get what you asked for not what you wanted. Here are some tools and techniques to help avoid and fix mistakes. Read our guide on How to Avoid and Locate Excel Errors.

Excel Date Calculations.
Sorry to disappoint, but this is not about romantic evenings with a spreadsheet — it’s about calculations Excel can make with dates, such as computing a person’s age or the due date of an invoice. It’s easy and once you know how it works you might have fun. Learn How to Make Date Calculations in Excel.

Keeping Time with Excel.
Ever need to compute how many hours are in a workday that begins at 8:30 AM and ends at 4:00 PM? Or that a 5-hour delivery that ships at 8:15 AM should arrive at 1:15 PM? Or that a lab process took 1 minute 12 seconds based on recorded starting and ending times? Excel can do all of that for us. Get the details on Time Calculations in Excel.

Manipulating Text in Excel.
People often use Excel to manage files such as client lists with lots of text, yet many don’t realize they can use formulas to combine and edit these words to produce useful combinations. A few simple formulas and functions are all it takes. Find out How to Manipulate Text in Excel.

Excel’s Custom Number Formats.
Many of us use Excel every day, but few know about secret codes that let us format numbers exactly the way we want. Actually they’re not secret, years ago they were they only way to format numbers. But like hieroglyphics, their existence and meaning have been lost through years of disuse. Get all the details on Specifying Excel Custom Number Formats.

Picking the Right Kind of Chart.
Charts are great for telling stories, and software like Excel makes them easy to create. What’s hard is making sure they paint the picture you want. A good place to start is by using the type of chart that emphasizes what you consider most critical. Get our insights on Choosing the Best Chart Type for Your Data.

Looking for More?

We offer public and private classes and Excel consulting.

Public classes.
Seven Excel classes are offered every month: Mastering Excel, Advanced Excel, Excel Pivot Tables, Excel Macros and Excel Dashboards. See our Class Schedule for dates and pricing.

Private Classes.
Schedule a private class for your team with a custom curriculum that emphasizes topics of greatest interest to your group. It can include subjects from any of our public classes, or see our list of topics for private Excel classes.

Consulting.
We can also provide consulting services for people who need advice or hands-on projects assistance.