Microsoft Office Excel 2016: Part 2 (Intermediate Level)

Learn how to 'talk' to Excel at a higher level to get the most out of your data. 

Learn how to ease your work by name ranges for use in functions and formulas. 

Cell names and range names are exactly what they sound like. They are meaningful names you assign to a given cell or range to make it easier to both understand what calculations are being performed in a formula and to reuse the references for a number of purposes. (Cell and Range Names, Names and the Name Box, Name Manager, The Create from Selection Command) 

Although it's certainly helpful to be able to name a range or a cell for easy navigation, the real power of this feature lies in the ability to easily identify references in formulas and to quickly and accurately insert references into multiple formulas. (Cell and Range Names in Formulas, Manually Entering Cell or Range Names, The Use in Formula Command Method, The Formula AutoComplete Method) 

Learn how to perform far more complex tasks in Excel beyond adding up rows and columns and multiplying the sum by some other figure. (Function Categories, The Excel Function Reference, Comparison Operator Basics, Function Syntax, The COUNTIF Function, The TODAY Function) 

The logical functions in Excel provide a method for testing various conditions to calculate a result of a value, text, or a calculation which enable you to ask questions of your data.

The IF function returns one value if the logical test you enter as an argument is true, and it returns a different value if the logical test is not true. (IF Function)

Learn how to combine logical tests. (AND Function, OR Function, NOT Function) 

The key to combining multiple calculations into a function in a single cell is nesting. Nesting is, simply, using a function as an argument within another function. (Nesting, Nested Function Syntax, Guidelines for Combining Functions) 

Learn how to use other powerful IF Functions. (Functions Similar to the IF Function (SUMIF, COUNTIF, AVARAGEIF ))

Learn how to work with of some of the more commonly used date and time functions. 

Learn how to master the most basic date and time functions. (TODAY, NOW, DATE) 

Learn how to work with more advanced date and time functions - not only important to all, who frequently deal with scheduling and analyzing data for particular periods of time. (The NETWORKDAYS Function, The WEEKDAY Function, The WORKDAY Function, The ISOWeeknum Function) 

Excel's text functions let you manipulate text in cells to extract portions of text to other cells or combine them. 

This basic text functions let you manipulate text in cells to extract portions of text to other cells or combine them to produce e.g. full names or addresses. (The LEFT Function, The RIGHT Function, The MID Function) 

To extract text from cells that contain values of varying characters, Excel also includes the FIND function. (The FIND Function) 

This feature splits a single column of text into multiple columns. For example, you can create first and last name columns from one column of full names. (The Text to Columns Feature) 

One of the most powerful text functions available in Excel is the CONCATENATE function. This function enables you to concatenate, or join together, text strings from multiple cells into a single cell. This function can save you massive amounts of time when you need to pull together data from multiple cells that already exists in your worksheets. (The CONCATENATE Function, Text Concatenation with the Ampersand, &) 

Excel contains three text functions that are very useful for formatting text to display it in the desired format. (The UPPER, LOWER and PROPER Function) 

By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries. 

By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries. (Sorting, Clean Data, Multiple Column/Row Sorting, Quick Sorts, Custom Sorts) 

In this topic, you will learn to filter data, which limits the rows of data you have to review in order to find what you are looking for. 

When you filter data in Excel, you do not affect the actual data entries; you alter only how Excel displays your data. Learn how to use this feature to your advantage. (Filter, AutoFilters, Custom AutoFilters, Advanced Filtering, The Criteria Range, Filter Operators) 

Similar to advanced filters, you can use database functions. These functions allow you to find the data you are looking for and perform calculations all in one step. 

Learn how to query data with database functions. Database functions, essentially, combine the functionality of Excel functions with the functionality of advanced filters. (Database Functions, Database Function Syntax) 

Learrn how to outline and subtotal your data which may be especially useful when you are working on a spreadsheet with a large amount of data. 

Learn how to outline data and perform calculations on subsets of data. (Outlines, The SUBTOTAL Function, The Subtotals Feature) 

By converting your raw data into tables, you will be able to take advantage of additional reporting features without affecting any of the data you have entered into your worksheets. 

Excel tables contain robust functionality that enables you to organize, change the display of, and perform calculations on worksheet data quickly and easily. (Tables, Table Components, The Table Tools Design Contextual Tab, Table Styles and Quick Styles, Quick Analysis) 

Learn how to apply basic function to your data in tables. (Summary, Average, Count)

Learning additional conditional formatting options beyond the basics is the next step to help you analyze data trends.

In addition to the preconfigured conditional formatting options available in Excel 2016, you have the option of creating completely custom conditional formats to suit your needs. A mayor step in becoming an Excel Expert! (Custom Conditional Formats, Rule Precedence) 

Learn how to examine and evaluate the data in one column, and then apply the specified conditional formatting to another column. And how to format the cells in numerous columns based on criteria in a different column. 

The tools available in Excel enable you to create specific rules you can use to apply conditional formatting and to tailor the display of conditionally formatted cells using an incredible array of options. (Use a Formula to Determine Which Cells to Format Rule, Cell References and Conditional Formatting, Guidelines for Applying Conditional Formatting to Cells Based on Values in Other Cells) 

Charts are a great way to interpret data, as many people need to see data visually to comprehend it better. Plotting data in charts can make spreadsheets less confusing when incorporated in your workbooks. 

Learn how to create Charts, that help worksheet viewers to quickly and easily interpret the data in a worksheet. (Chart Basics, Chart Types, Chart Insertion Methods, Recommended Charts).

A well-formatted chart can mean the difference between simply delivering information and making an impact on your audience. 

Excel 2016 provides you with a vast array of options when it comes to modifying and formatting your charts. (Modification vs. Formatting, Chart Elements, Chart Elements Guidelines, The Chart Tools Contextual Tab, The Format Task Pane, The Chart Tools Buttons)

Excel 2016 includes a wide range of advanced charting features that enable you to display widely varying sets of data together, include forecasting trends on your charts.

A dual-axis chart is, simply, a chart that displays two sets of information on the same chart. The main advantage to dual-axis charts is the ability to not only display two different sets of data simultaneously, but also to format the different sets of data independently of each other. (Dual-Axis Charts, Forecasting, Trendiness, Trendline Types, The Format Trendline Task Pane) 

Working with advanced charting features can require quite a bit of chart formatting and modification. And, it's likely you'll need to reuse at least some of your charts. (Chart Templates) 

If your data is misrepresented or presented ineffectively, key insights and understanding are lost, which hurts both your message and your reputation. (Order Data Appropiately, Space Bars Appropiately, Reduce Unnecessary Labeling, Increase Readability, Chart Type Considerations, Line Margins, Maintain Itegrity) 

PivotTables combine some of the most powerful and useful types of Excel functionality, such as sorting, filtering, summary functions, and subtotals, to give you an incredible level of control over how you view your data. 

To take advantage of the functionality and flexibility of PivotTables, you must first understand how to create them. (PivotTables, Recommended PivotTables, The PivotTable Fields Task Pane) 

Excel 2016 provides you with a number of different tools and commands you can use to organize the structure of your PivotTables. Knowing how these tools work and understanding how PivotTable structure translates into actionable intelligence are the keys to getting the answers you seek. 

(The PivotTable Tools Contextual Tab, Summarize and Show Combinations, The GETPIVOTDATA Function) 

Like standard Excel charts, PivotCharts are graphical representations of numeric values and relationships among those values. The main difference is simply that PivotCharts are linked to PivotTable data, whereas standard charts are linked to either a range of data or a table. 

Learn how to translate your PivotTable data into PivotCharts. (PivotCharts, The PivotChart Fields Task Pane, PivotChart Filters) 

Slicers are a tool that gives you a high level of control over PivotTable filtering. is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables. 

Learn how to apply Slicers as a high level of control over PivotTable filtering, one that is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables and PivotCharts. (Slicers, The Slicer Tools Contextual Tab, The Report Connections Dialog Box, Timelines, The Timeline Tools Contextual Tab) 

Congratulations, you finished Excel 2016: Part 2 (Intermediate Level). Hope you enjoyed this training and that I see you in Excel 2016: Part 3 (Expert Level). In that course, you will build upon the skills you have acquired by working with multiple worksheets and workbooks, use lookup functions and formula auditing to troubleshoot your workbooks and fix errors, and to share and protect workbooks from unwanted changes. You will also learn to automate Excel functions, create sparklines and map data, as well as to forecast future data. 

Click here to go directly to the next level of Excel 2016: Part 3 (Expert):

https://www.udemy.com/excel-2016-part-3-expert/