Microsoft Excel Formula Tutorials: Functions as Building Blocks for Formulas

Microsoft Excel Formula Tutorials: Functions as Building Blocks for Formulas
Page content

Learn What You Can Accomplish in Excel with Formula Tutorials

Microsoft Excel is more than a way to list information or sort fields. By harnessing the power of Excel formulas, you can create complex applications which can be used to convey information, monitor and format data, and make charts and financial calculators. Microsoft’s Office 2007, and the new Office 2010 have a number of features which allow you to create very sophisticated spreadsheets, forms, and charts - and very easily, once you try the tutorials in this article.

Basic Excel Formulas

While you can always type information directly into a cell, Excel’s formulas and functions allow you to manipulate data. You can repeat information from another cell - or you can figure out the interest you will pay on a $200,000 mortgage over 20 years.

=A2 is the most basic of formulas - maybe you don’t even think of it as a formula. However, it tells Excel to go to the address A2, copy what it finds there,and repeat it in the cell where you typed the formula. If the data in A2 changes, so will the information shown in the cell containing the formula. But =A2 is just the beginning.

Building on Functions for Formulas to Create Spreadsheets

The functions in Excel are the building blocks for formulas. The formulas in Excel are the tools for creating specialized spreadsheets.

Bright Hub’s Microsoft Excel Tutorials will teach you many things you can do with the formulas in Excel.

Functions and Formulas

There are a number of articles on Bright Hub which can help you master Microsoft Excel formulas. Tutorials are available to teach how to use functions like CONCATINATE, which joins the information from two different cells into another. Do you have a list of first names, and a list of last names? With CONCATINATE, you can have [firstname] and [lastname] appear in one cell as [fullname], ready to print on a mailing label or become a name in a name generator.

Many articles in the Excel topic are tutorials for functions. Whether you want to use LOOKUP, HLOOKUP or VLOOKUP, there is a tutorial with a step by step explanation. Learn to use the functions LEFT, MID and RIGHT, TEXT, TRANSPOSE,and COUNT.

Testing Formulas

If you are writing complex formulas, and using a lot of data to try them out, a really useful Excel tutorial to study is Excel Formula Auditing. It can be difficult to figure out what is giving you unexpected results, especially if you are testing your spreadsheet with data that should provide specific answers, and is not. A way to examine the calculations taking place at each step in a formula will let you isolate the problem.

Chart Formulas

Charts are useful in many contexts. Some types of charts have a number of uses, whether you present scientific data, sales information or want to assess your business performance. The proverb ‘a picture is worth a thousand words’ is applicable to charts as well as artwork. Your chart for the Vice President in charge of Sales, portraying the increase in orders since the company started offering tech support - upon your advisement - makes a great handout when you are asking for a raise.

Teachers have advised students to learn research and paper writing skills for probably as long as the general education system began. High School students often have deaf ears, unfortunately. A chart showing the relationship between students who did well in composition classes and their GPA their freshman year of college might make believers of a few more.

You can make a scatter plot, histogram or pie chart using Microsoft Excel formulas. Tutorials also cover formatting the charts to serve the specific purpose. you want. The ability to customize your charts to express the information you gained from other spreadsheets in Excel will serve you well.

Article Image

Image from article on How to Make a Pareto chart in Excel (link in References)

Excel Formulas for Sales and Businesses

There are a number of different ways formulas in Excel can be useful in sales. In this article on Creating a Sales Forecast in Microsoft Excel, Michele McDonough explains how to use your own sales data and the projected percentage of increases to help you plan expenditures or financial growth in your business. While she explains the formulas, she also provides a free template you can download to modify yourself.

You can also learn how to use Excel to predict sales with the directions for a model based on multiple regression.Article Image Article Image

Images from linked article on using Excel to make a model based on multiple regression

Project Management Applications

Many Project management applications can be created in Excel, a bonus for start-up businesses that do not want to spend a lot of money upfront, but recognize the need for business planning, goal setting, and tracking the progress of tasks. By keeping track of what is accomplished and then reviewing your progress, you can learn what you should repeat, what you should do differently, and whether you have met the goals you set. Project management also gives you the information you need to know if you are on track with your plans, whether the steps to your goal are going as planned, and when things are not proceeding along the lines you anticipated.

Finances and planning with Excel

One useful application for business is the Balanced Scorecard. Using this tutorial, you can create a Balanced Scorecard which you can use for presentations, determining strategy, and assessing performance. This article covers the basics and then goes on to show you how to personalize the scorecard and make an exciting visual presentation.

Article Image

Image from linked article on how to create a balanced scorecard in Excel

Are you worried about your paycheck covering all the bills coming in? It is time to budget your money - and planning is easier when you use Excel to plan your monthly budget. With this simple to set up spreadsheet, you can incorporate formulas to estimate expenditures, and record the actual expenses. You will never unexpectedly go over your budget or pay a bounced check fee again if you use your budget.