10.1 Data Tables and Ranges

Data Tables can be used for automatically generating multiple possible scenarios. This can be used to predict future outcomes, such as revenues, unit sales projections, installment payments, interest rate changes, and many other variables.
Businesses need to construct complex datasets for their everyday control and management of the company. For that purpose, Excel has data ranges, which help the user define a name for a particular set of cells. This allows the user to use the given name of the data range instead of cell references.
Arrays also simplify the complexity of huge worksheets, as they let the user autofill and compute multiple calculations simultaneously.

10.2 More About Formulas

Businesses need spreadsheets to handle datasets that are updated every minute with information. Cell references, comparison operators, and array formulas can help analyze large sets of changing data.
Complex equations need complex formulas to solve them. Excel formulas use the same math symbols as “pencil and paper” math and follow the same order of operations. An easy way to remember the order of operations is with the acronym PEMDAS.
Cell references are needed in formulas and in constructing autofilled tables. There are three types of cell references: relative, absolute, and mixed. Relative cell references are when a cell copies its links to other cells relative to where it is located. Absolute cell references lock a reference to a specific cell, row, or column. Mixed cell references use both.
Comparison operators are math inequalities used in Excel formulas. They use logical operators like the IF function to make your workbook pull information for you.
Array formulas make simultaneous calculations, which saves time. They do the same operations that other default Excel functions do but allow the user to customize the calculation given the designed criteria in the formula.

The tools introduced in this section will help you use and make sense of internal business data, such as client orders, invoice amounts, and machinery operative data. The arithmetic functions and statistical tools can give you an idea of the operations’ tendencies and cycles.
The basic financial functions can be used to determine monthly or annual loan payments or the number of years needed to pay off a loan.
Logical functions are used for making inferences or deductions. Many logical functions can be embedded within one another to create complex logical scenarios.

Corporations need to analyze their datasets to produce metrics or insights. However, these datasets need to be organized and cleaned before they can be processed.
A PivotTable is a tool that reads raw datasets. It has user-friendly drag-and-drop capabilities that can help organize the table information for the business.
These PivotTables are malleable, as the user can rearrange the variables in different ways to generate entirely different data highlights.
Visual representations of PivotTables can be generated using another Excel tool, PivotChart.

All organizations should use auditing formula techniques to double-check their datasets. Errors can occur in a variety of ways, from user error to cell reference errors.
Running the Trace Precedents and Trace Dependents tools on a cell can diagram the problem with that formula or the information contained in that cell.
Auditing formulas is a manageable process if you use the right tools. You need to specify the correct options in the Excel settings to be able to see all the error codes, and be aware of all the built-in error-checking tools Excel has to offer, such as the Inquire add-in.

Using consistent branding and style throughout a company’s documents is important for promoting professionalism. This can be achieved by using a company-wide branding or style manual.
The Themes tool lets you change a formatting scheme throughout an entire workbook. You can further adjust the theme by using the Styles combo-box options.
Excel lets you save time by copying a format using the Format Painter or the Paste Special method.