Excel 2016 Advanced

This user guide will introduce you to some of Excel’s more complex functionality. Specifically when analysing data. You can download the book for free via the link below. Content Apply Conditional Logic in formula IF Function (IF Statement) Nested IF Function Countif Function Sumif Function Create and apply Range Names Defining a Named Cell or Range of Cells Using a Named Cell in a formula Naming a range of cells Editing and renaming a named range Define a Name Using the Dialogue Box Charts Using Quick Analysis Recommended Charts Creating charts with the keyboard Creating line charts Creating and editing a column chart Creating pie charts Modifying charts using the Ribbon Modifying charts using icons Formatting charts – Task Pane Apply and manipulate illustrations Pictures in Excel Resize the picture – dragging Modify a picture Reset a picture SmartArt SmartArt Shapes Inserting shapes into a workbook Screenshots Inserting a screenshot Manage comments Inserting and editing comments Deleting comments Show/Hide Comments Moving between comments Printing comments Sorting data Databases in Excel – introduction Sort data Sort on font colour Sort on cell colour Sort on cell icon Filtering data Filter data – the filter icon Filter on text Filter on more than one column Remove the filter Filter data on colour Text filters Number filters Date filters Format as table Reasons to use Tables in Excel Format as table icon Insert a table Change table style Remove formatting Name the table Calculated columns Add a total row Convert back to a range (remove automatic functionality) Filter a table Filter a table Filter a table with a total row Insert a slicer Customize a slicer Delete a slicer Grouping and outlining Outlining Create an automatic outline Subtotals Why use subtotals? Sort first Remove subtotals Apply conditional formatting Conditional formatting Top and bottom rules Data bars Colour scales Icon sets Clear conditional formatting Use a formula to control conditional formatting Sparkline charts Sparkline line chart Column spark lines Creating a win/loss spark line chart Remove a spark line chart Text functions Text functions – an introduction Concatenate Trim Upper Lower Proper Right Left Mid Quick analysis Quick analysis Quick analysis Formatting Quick analysis totals Quick analysis Charts Quick analysis Tables Quick analysis sparklines Macros What is a Mmcro Macro security Get ready Get set and go Run the macro