Advanced MS Excel

Essentially, there are very many functions and formulas available in Microsoft Excel that make organizing and interpreting data easier. Different companies use Excel for different purposes to help decrypt important metrics in their sector, hence there are countless jobs that need some knowledge of Excel.

In a nutshell, if you can understand how to use Excel and create valuable insights for a company, you will be a valued employee, and can expect to be compensated accordingly. But, of course, you cannot expect to become an Excel powerhouse overnight Invest your time in research and learning Excel both online and offline.

Course Outline:

• Understanding the Microsoft Excel Interface
• Identifying Parts of the Excel Window, Using the Built-in Help, Perform a Search, Making Selections from the Ribbon, Customizing the Ribbon, Minimize the Ribbon Size, Add More Commands to the Ribbon, Customizing the QAT, Move the QAT to a New Location, Add More Commands to the QAT, Viewing Multiple Sheets at the Same Time, Arrange Multiple Sheets, Scroll Two Sheets Side by Side, Changing the Zoom on a Sheet, Use Excel’s Zoom Controls, Moving Around on a Sheet, Keyboard Shortcuts for Quicker Navigation, Selecting a Range of Cells, Select a Range Using the Mouse.
• Working with Workbooks and Templates
• Managing Workbooks, Create a New Workbook, Open an Existing Workbook, Use the Recent Workbooks List, Save a Workbook, Close a Workbook, Using Templates to Quickly Create New Workbooks, Use Microsoft’s Online Templates, Save a Template, Open a Locally Saved Template to Enter Data, Edit the Design of a Locally Saved Template, Change Personal Templates Location.
• Working with Sheets
• Adding and Deleting Sheets, Add a New Sheet ,Delete a Sheet, Navigating and Selecting Sheets Activate Another Sheet, Select Multiple Sheets, Moving or Copying Sheets, Move or Copy a Sheet in the Same Workbook, Move or Copy a Sheet Between Workbooks, Renaming a Sheet, Change a Sheet’s Name.
• Getting Data onto a Sheet
• Entering Different Types of Data into a Cell, Type Numbers or Text into a Cell, Enter Numbers as Text, Type Dates and Times into a Cell, Undo an Entry, Using Lists to Quickly Fill a Range, Extend a Series Containing Text, Extend a Numerical Series, Create Your Own List, Using Paste Special, Paste Values Only,Combine Multiple Paste Special Options,Multiply the Range by a Specific Value,Use Paste to Merge a Noncontiguous Selection,Using Text to Columns to Separate Data in a Single Column,Work with Delimited Text,Using Data Validation to Limit Data Entry in a Cell,Limit User Entry to a Selection from a List,Using Web Queries to Get Data onto a Sheet,Insert a Web Query,Editing Data,Modify Cell Data,Clearing the Contents of a Cell,Clear Only Data from a Cell,Clearing an Entire Sheet,Clear an Entire Sheet,Working with Tables,Define a Table,Add a Total Row to a Table,Change the Total Row Function,Expand a Table ,Fixing Numbers Stored as Text,Use Convert to Number on Multiple Cells,Use Paste Special to Force a Number,Spell Checking a Sheet,Finding Data on a Sheet,Perform a Search,Perform a Wildcard Search,Replace Data on a Sheet.
• Selecting and Moving Data on a Sheet
• Working with Rows and Columns,Select a Row or Column,Insert a New Row or Column, Delete a Row or Column,Move Rows or Columns by Dragging,Move Rows or Columns by Cutting,Copy Rows or Columns,Working with Cells,Select a Cell Using the Name Box,Select Noncontiguous Cells and Ranges,Insert Cells,Delete Cells,Move Cells.
• Formatting Sheets and Cells
• Changing the Font Settings of a Cell,Select a New Font Typeface,Increase and Decrease the Font Size,Apply Bold, Italic, and Underline to Text,Apply Strikethrough, Superscript and Subscript,Change the Font Color,Format a Character or Word in a Cell,Format Quickly with the Format Painter,Adjusting the Row Height,Modify the Row Height by Dragging,Modify the Row Height by Entering a Value,Use Font Size to Automatically Adjust the Row Height,Adjusting the Column Width,Modify the Column Width by Dragging,Modify the Column Width by Entering a Value, Aligning Text in a Cell, Change Text Alignment,Merging Two or More Cells, Merge and Center Data, Merge Across Columns, Unmerge Cells, Centering Text Across Multiple Cells, Center Text Without Merging, Wrapping Text in a Cell to the Next Line,Wrap Text in a Cell,Reflowing Text in a Paragraph,Fit Text to a Specific Range,Indenting Cell Contents,Indent Data, Applying Number Formats,Modify the Number Format,Change the Format of Negative Numbers,Apply a Currency Symbol,Format Dates and Times,Format as Percentage,Format as Text,Apply the Special Number Format,Adding a Border Around a Range,Format a Range with a Thick Outer Border and Thin Inner Lines,Add a Colored Border,Coloring the Inside of a Cell,Apply a Two-Color Gradient to a Cell.
• Advanced Formatting
• Creating Custom Number Formats,The Four Sections of a Custom Number Format,
• Optional Versus Required Digits,Use the Thousands Separator, Color Codes, and Text
• Line Up Decimals,Fill Leading and Trailing Spaces,Show More Than 24 Hours in a Time Format, Creating Hyperlinks,Create a Hyperlink to Another Sheet,Link to a Web Page,Dynamic Cell Formatting with Conditional Formatting,Use Icons to Mark Data,Highlight the Top 10,Highlight Duplicate or Unique Values,Create a Custom Rule,Clear Conditional Formatting,Edit Conditional Formatting,Using Cell Styles to Apply Cell Formatting, Apply a Style Create a Custom Style,Using Themes to Ensure Uniformity in Design,Apply a New Theme,Create a New Theme, Share a Theme.
• Using Formulas
• Entering a Formula into a Cell,Calculate a Formula,View All Formulas on a Sheet,Relative Versus Absolute Referencing,Lock the Row When Copying a Formula Down,Copying Formulas, Copy and Paste Formulas,Copy by Dragging the Fill Handle,Copy Rapidly Down a Column,Copy Between Workbooks Without Creating a Link,Converting Formulas to Values,Paste as Values,Select and Drag,Using Names to Simplify References,Create a Named Cell,Use a Name in a Formula,Inserting Formulas into Tables,Write a Formula in a Table,Write Table Formulas Outside the Table,Using Array Formulas,Enter an Array Formula,Delete a Multicell Array Formula,Working with Links,Control the Prompt,Refresh Data,Change the Source Workbook,Break the Link,Troubleshooting Formulas, Fix ###### in a Cell,Understand a Formula Error,Use Trace Precedents and Dependents,Track Formulas on Other Sheets with Watch Window,Use the Evaluate Formula Dialog Box,Evaluate with F9, Adjusting Calculation Settings, Set Calculations to Manual.
• Using Functions
• Understanding Functions,Look Up Functions,Use the Function Arguments Dialog Box,Enter Functions Using Formula Tips,Using the AutoSum Button,Calculate a Single Range,Sum Rows and Columns at the Same Time,Quick Calculations,Calculate Results Quickly,Using Quick Analysis Functions,Using Lookup Functions,Use CHOOSE to Return the nth Value from a List,
• Use VLOOKUP to Return a Value from a Table,Use INDEX and MATCH to Return a Value from the Left,Using SUMIFS to Sum Based on Multiple Criteria,Sum a Column Based on Two Criteria,Using IF Statements,Compare Two Values,Hiding Errors with IFERROR,Hide a #DIV/0! Error Understanding Dates and Times,Return a New Date X Workdays from Date,Calculate the Number of Days Between Dates,Using Goal Seek,Calculate the Best Payment,Using the Function Arguments Dialog Box to Troubleshoot Formulas,Narrow Down a Formula Error.
• Sorting Data
• Using the Sort Dialog Box,Sort by Values,Sort by Color or Icon,Doing Quick Sorts,Quick Sort a Single Column,Quick Sort Multiple Columns,Performing Custom Sorts,Perform a Random Sort, Sort with a Custom Sequence,Rearranging Columns,Sort Columns with the Sort Dialog Box, Fixing Sort Problems.
• Filtering and Consolidating Data
• Using the Filter Tool,Apply a Filter,Clear a Filter,Reapply a Filter,Turn the Filter On for One Column,Filtering Grouped Dates,Turn On Grouped Dates,Filter by Date,Using Special Filters, Filter for Items that Include a Specific Term,Filter for Values Within a Range,Filter for the Top 25 Items,Filter Dates by Quarter,Filtering by Color or Icon,Filtering by Selection,Allowing Users to Filter a Protected Sheet,Filter a Protected Sheet,Using the Advanced Filter,Reorganize Columns,Create a List of Unique Items, Filter Records Using Criteria,Use Formulas as Criteria, Removing Duplicates,Delete Duplicate Rows,Consolidating Data,Merge Values from Two Datasets,Merge Data Based on Matching Labels.
• Distributing and Printing a Workbook
• Using Cell Comments to Add Notes to Cells,Insert a New Cell Comment,Edit a Cell Comment,Format a Cell Comment Insert an Image into a Cell Comment,Resize a Cell Comment,Show and Hide Cell Comments,Delete a Cell Comment,Allowing Multiple Users to Edit a Workbook at the Same Time,Share a Workbook,Hiding and Unhiding Sheets,Hide a Sheet,Unhide a Sheet,Using Freeze Panes,Lock the Top Row,Lock Multiple Rows and Columns,Configuring the Page Setup, Set Paper Size, Margins, and Orientation,Set the Print Area,Set Page Breaks,Scale the Data to Fit a Printed Page,Repeat Specific Rows on Each Printed Page,Creating a Custom Header or Footer,Add an Image to the Header or Footer,Add Page Numbering to the Header and Footer,Printing Sheets,Configure Print Options,Protecting a Workbook from Unwanted Changes,Set File-Level Protection,Set Workbook-Level Protection,Protecting the Data on a Sheet,Protect a Sheet,Unlock Cells,Allow Users to Edit Specific anges,Preventing Changes by Marking a File as Final,Mark a Workbook as Final,Sharing Files Between Excel Versions,Check Version Compatibility,Recovering Lost Changes,Configure Backups,Recover a Backup,Recover Unsaved Files,Sending an Excel File as an Attachment,Email a Workbook, Sharing a File Online,Save to OneDrive.
• Inserting Subtotals and Grouping Data
• Using the SUBTOTAL Function,Calculate Visible Rows,Summarizing Data Using the Subtotal Tool,Apply a Subtotal,Expand and Collapse Subtotals,Remove Subtotals or Groups,Sort Subtotals,Copying the Subtotals to a New Location,Copy Subtotals,Applying Different Subtotal Function Types,Create Multiple Subtotal Results on Multiple Rows,Combine Multiple Subtotal Results to One Row, Adding Space Between Subtotaled Groups,Separate Subtotaled Groups for Print,Subtotaled Groups for Distributed Files,Grouping and Outlining Rows and Columns, Apply Auto Outline,Group Data Manually.
•  Creating Charts and Sparklines
• Adding a Chart,Add a Chart with the Quick Analysis Tool,Preview All Charts,Switch Rows and Columns,Apply Chart Styles or Colors, Apply Chart Layouts,Resizing or Moving a Chart, Resize a Chart,Move to a New Location on the Same Sheet,Relocate to Another Sheet, Editing Chart Elements,Use the Format Task Pane,Edit the Chart or Axis Titles, Change the Display, Units in an Axis, Customize a Series Color,Changing an Existing Chart’s Type, Change the Chart Type,Creating a Chart with Multiple Chart Types, Insert a Multiple Type Chart, Add a Secondary Axis,Updating Chart Data,Change the Data Source,Adding Special Charts, Create a Stock Chart,Create a Bubble Chart, Pie Chart Issue: Small Slices,Rotate the Pie,
• Create a Bar of Pie Chart,Using a User-Created Template,Save a Chart Template,Use a Chart Template,Adding Sparklines to Data,Insert a Sparkline,Emphasize Points on a Sparkline,Space Markers by Date,Delete Sparklines.