Excel spreadsheet formulas allow the user to manipulate large amounts of data to produce accurate reports, while saving valuable time.

Microsoft excel is really a database and a report maker. Excel
spreadsheet formulas
allow us to draw information out of a database, or to conveniently
report information.

The most popular formula to extract information from a database is:

- Vlookup

- IF formula
- Sum
- Average

The

Part Number Quantity Price

a | b | c | |

1 | Part 123 | 45 | 14.50 |

2 | Part 234 | 72 | 23.76 |

3 | Part 456 | 81 | 111.23 |

Above is our database of information. In a separate cell below, we have part numbers, but want to find the price of the part without manually going through our database and picking out the price:

Part Price

f | g | |

1 | Part 234 | =vlookup(f1,a1:c3,3,false) |

2 | Part 456 | =vlookup(f2,a1:c3,3,false) |

We enter the formulas above to extract the price for each part from the database above. The formula in g1 looks in the database for data that matches f1, then at column 3 and returns that data (in this case 23.76).

This is a very small database, but if the database was many hundreds of part number records, you can see the advantage of using this formula.

The

Part # QTY Price

a | b | c | d | |

1 | Part 123 | 45 | 14.50 | =if(c1>80, c1,0) |

2 | Part 234 | 72 | 23.76 | =if(c2>80, c2,0) |

3 | Part 456 | 81 | 111.23 | =if(c3>80, c3,0) |

In this example, we want to find parts above that have a price greater than $80.00. The IF formula above in cell d1 looks at the price in cell c1 and determines if it is larger than 80, if it is, then it returns the value in cell c1, if not, then it returns 0. The formulas in cells d1 and d2 will return 0, because the value in column c is less than 80. The formula in d3 will return 111.23 because this value is greater than 80

The

Part Number Quantity Price

a | b | c | ||

1 | Part 123 | 45 | 14.50 | |

2 | Part 234 | 72 | 23.76 | |

3 | Part 456 | 81 | 111.23 | |

4 | =sum(c1:c3) |

The

Part Number Quantity Price

a | b | c | ||

1 | Part 123 | 45 | 14.50 | |

2 | Part 234 | 72 | 23.76 | |

3 | Part 456 | 81 | 111.23 | |

4 | =average(c1:c3) |

There are many more special excel spreadsheet formulas, but these are usually the most widely used.