We’ll show you the two surefire methods of handling these errors.įinding the Right Value(s) - Sometimes you need to measure variations in a single cell value, like product price, to optimize the value of another cell, like profits. Likewise, the SUMIF(), COUNTIF(), and similar Excel functions are counterparts of “aggregate data” calculations in databases – useful, for example, if you wanted to sum up the order values shipped to a particular zip code.Įrrors - You’ve probably encountered your fair share of errors that Excel has vociferously pointed out with: “#N/A” and “#NUM!” It turns out there’s an easy way to deal with these errors, either by intercepting them and providing a backup value, or by auditing and fixing them once and for all. A “lookup” in Excel is when your formula looks up values in a secondary worksheet (just like a database!) and puts them in the primary worksheet: for example, looking up in worksheet B the inventory amount adjacent to a cell containing a particular SKU, and putting it in worksheet A, adjacent to the cell containing that same SKU.

Whether you’re looking at someone else’s spreadsheet and wondering “how did they do that?” or simply lacking enough firepower to get your own spreadsheets to do what you want, this class will probably solve that problem.ĭatabase Features - You may be thinking “I use spreadsheets, not databases!” - but there are a few indispensable Excel features that mirror the most useful of features from the world of databases: lookups and aggregate functions. After you’ve mastered formatting tables of data, creating formulas between cells, using built-in functions, and creating charts, there is a standard set of more powerful features you’ll eventually need.

Advanced excel 2019