Excel image

These are a few of the highlights covered in Part 2 of our 3-Part Webinar Series on Microsoft Excel: Excel Like a Pro Part 2.

The core platform of an Excel file is defined as a workbook. This workbook in turn is comprised of worksheets (by default Sheet1 is the beginning of the workbook file, but you can continue to add up to 256 worksheets as needed).  Worksheets are represented as tabs docked at the bottom of the Excel interface and can be renamed (up to 31 characters), color coded, hidden, moved or copied, even referenced in formulas.  A popular activity in Excel is the need to summarize data across multiple worksheets.  This is referred to as creating a 3-D formula.  A 3-D formula is a useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet that contain the same type of data, such as when you consolidate budget data from different departments in your organization.

Excel provides a data tool named Consolidate that offers a similar result as the 3-D formula.  To summarize and report results from separate worksheets, you can consolidate data from each worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet, or in other workbooks. For example, if you have an expense worksheet for each of your regional offices, you might use consolidation to add these figures into a master corporate expense worksheet.

One of the most frequent types of data entry in Excel is date driven. Not only does Excel offer a variety of ways to express date formats, but it also includes numerous functions to represent data involving dates. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook, and it is also useful for calculating intervals. Suppose you want to adjust a project's schedule date by adding two weeks to see what the new completion date will be. You can add or subtract a number of days to or from a date by using a simple formula, or you can use worksheet functions that are designed to work specifically with dates in Excel.

One of the most exciting and useful features of Excel is Conditional Formatting. Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.  Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data.

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect by testing for a condition and returning a result if that condition is true or false.  Sometimes you might need to evaluate more complex scenarios that require nesting, which is the practice of joining multiple functions together in one formula.  The IF function in Excel can be nested when you have multiple conditions to meet. The AND function is one such logical function that tests multiple conditions. In its use nested within an IF formula, the formula will display true if all of the conditions are satisfied, and false if even one condition is not satisfied. The OR function is another logical function that tests multiple conditions, but it is more flexible. In its use nested within an IF formula, the formula will display true if any one of the conditions are satisfied, and false if all conditions are false.

This and a lot more was covered in our Excel Like a Pro Part 2 webinar.  Stay tuned for our Excel Part 3 webinar as we continue our Excel learning series.