Microsoft Excel Resources
These links connect to how-to articles on our web site. All of them appeared in our twice-monthly newsletter. To receive your own copies by e-mail, please add your name to the list.
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. We can then refer to easy to remember names like Revenue, Sales and Expenses rather than cell ranges like A1:A100. Read More.
Conditional Formatting in Excel.
Visual cues help us grasp situations at a glance. When driving, for example, colored lights tell us whether to stop, go or pull over so emergency vehicles can pass. Without standardized signals we could never read the conditions as quickly. The same is true with large data sets. That is why Excel’s conditional formatting is so useful — it lets us set up easy-to-read signals to highlight our data. Read More.
Excel’s IF Function.
Switches are among a railroad’s most useful tools — they let a dispatcher send a train over one of several tracks. Similarly Excel users sometimes want a calculation to apply one equation in certain circumstances and a different one in others. For example, a bonus for long-time employees may be computed differently from that for others. Calculations like that are sometimes said to have conditional results and the IF function is the tool for making them in Excel. Read More.
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 More.
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. Read More.
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? Read More.
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 More.
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. Read More.
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. Read More.
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. Read More.
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. Read More.
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. Read more.
Want More Help?
We offer public and private classes and Excel consulting.
Five Excel classes are offered every month. Dates are on the current Class Schedule. The following links take you to detailed descriptions of the topics covered in each: Excel, Advanced Excel, Excel Pivot Tables, Excel Macros and Excel Dashboards.
Classes for private groups can be given at your office or ours with a custom curriculum that emphasizes the topics of greatest interest to the group. Our web site has a list of topics for private classes.
We can also provide consulting services for people who need advice or hands-on assistance with specific projects.