LIVE Advanced Excel Formulas Masterclass

EXCEL BASICS (for Beginners)

Getting Started with Excel

Structure of the Excel working area, How to navigate in Excel, Ribbons and tabs, Quick Access Toolbar (QAT) Dialog Box & Task Panes, Customizing ribbons and QAT, What is an active cell How to use ALT shortcuts

Data Entry, Data Editing, and Number Formatting

Once you know are comfortable with the layout of Excel, the next step is to learn how to enter and edit data in Excel. You can enter three types of data in Excel – numeric, text, and formulas. To enter data in any cell, just select the cell and start typing. Once done, press enter to move to the cell below. In case you are entering dates, enter it in a format that Excel recognizes as a date (such as 01-01-2020 or 01 Jan 2020 or 01/01/2020). This video will show you how to enter data in Excel and edit already added data (such as deleting data, modifying existing data and replacing data). Apart from this, it also covers the auto-fill in Excel as well as number formatting.  

Data Formatting

Click here to download the example Excel file This video covers the basics of data formatting in Excel. You can format data in Excel to make it look better and to highlight some data points. For example, if you have data-set and you want to highlight the negative numbers, you can simply highlight it in red color to it. Here are some of the common formatting I use to make my data look better and more readable Applying borders to all cells Making headers bold Giving a background cell color to headers. Center align the headers You can find these options in Home tab in the Font category.  

Working with Cells and Ranges in Excel

Click here to download the example Excel file In this video of this Free Online Excel Training, you’ll learn how to work with cells and ranges in Excel. Since a worksheet in Excel is made up for cells, if you want to become proficient in Excel, you need to know how to best work with cells and ranges. And this video will teach you exactly that. It covers the following topics: Cell address nomenclature Selecting cell/ranges in Excel Copying and moving cells/ranges in Excel Using paste special Selecting special cells Using find and replace to select cells Adding comments and Using quick analysis  

Managing Worksheets

Click here to download the example Excel file In this video of this Free Online Excel Training, you’ll learn how to manage worksheets in Excel. It covers the following topics: Navigating through worksheets in Excel Changing worksheet name and color Adding/deleting worksheets Hiding/Un-hiding worksheets Moving/copying worksheets Comparing worksheets side by side Splitting worksheet into panes Freeze panes Saving and opening workbooks  

EXCEL BASICS (for Beginners)

Introduction to Excel Tables

Click here to download the example Excel file Excel tables are amazing, especially if you plan to work with a lot of data/dashboards/models. Cover the following topics: Creating an Excel Table Excel Table features Structured references Table slicers Note that table slicers became available only from Excel 2013 version onward.

Auto-fill, Custom Lists, and Flash Fill

Click here to download the example Excel file In this lesson, I cover the following topics: Autofill: This allows you to quickly fill data in contiguous cells. For example, you can have ‘Jan’ and ‘Feb’ in two adjacent cells and then you can use Autofill to get the other month names Custom Lists: Custom lists allows you to create your own lists that you can use in Autofill. For example, if you have 20 names, you can create a custom list and then use Autofill to get all the names by just dragging the mouse Flash Fill: Flash fill is a new feature in Excel 2013 and allows you to identify a pattern in data and perform data slice and dice based on the pattern  

Number Formatting in Excel

This covers the following topics: How to access number formatting options Using number formatting options using a dialog box Using custom number formatting Remember that formatting a number in Excel only changes how it’s displayed, but the underlying value remains the same.  

DATA ANALYSIS

Named Ranges in Excel

Learn about using Named Ranges in Excel. It covers the following topics: Creating Named Ranges Managing Named Ranges Important Keyboard Shortcuts Creating Dynamic Named Ranges using OFFSET and INDEX formulas Related Excel Formulas: Named Ranges in Excel Picture Lookup in Excel Excel OFFSET Function

Data Validation in Excel

Learn about using data validation and drop-down lists. It covers the following topics: Data Validation Criteria Input Message Customizing Error Messages Circle Invalid Entries Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List) Related Excel Functions: Drop Down list in Excel Dependent Drop Down List in Excel Extract Data from Drop Down List Selection in Excel. Select Multiple Items from a Drop Down List in Excel. Creating a Dynamic Excel Filter Search Box. Display Main and Subcategory in Drop Down List in Excel. How to Insert Checkbox in Excel. Using a Radio Button (Option Button) in Excel

Data Sorting and Filtering in Excel

Click here to download the example Excel file Learn about data sorting and filtering in Excel. It covers the following topics: Data Sorting Multi-Levels of Sorting Data Filtering Advanced Filtering Filtering and Sorting in Excel Data Tables Related Excel Functions: Data Sorting in Excel Multiple Level Data Sorting in Excel Sort Worksheets in Excel (VBA) Sort Data in Alphabetical Order Automatically Sort by Color in Excel Sort Data using VBA Delete rows based on cell value in Excel

Using Conditional Formatting in Excel

Learn about the amazing conditional formatting. It covers the following topics: Introduction to Conditional Formatting Using Formula in Conditional Formatting Advanced Examples (Highlight Every Nth Row, Creating Dynamic Search) Related Excel Formulas: Conditional Formatting in Excel Find Duplicates using Conditional Formatting Search and Highlight Data Using Conditional Formatting Highlight Every Other Row in Excel Highlight Blank Cells in Excel Creating Heat Maps in Excel

INTRODUCTION TO EXCEL CHARTING

Introduction to Excel Charting

Learn about Excel charting and how to make useful charts quickly. Excel has a lot of inbuilt charts that you can use instantly to visualize your data. And there are many combination charts and advanced charts you can create to pack a lot of information in a single chart. It covers the following topics: Excel Chart Types Chart Editing Formatting Chart Elements Combination Charts Handling Gaps in Charts Related Excel Topics: Combination Charts in Excel Add Trendlines in Excel Charts Add Secondary Axis to Charts in Excel Creating a Pie Chart in Excel Excel Area Chart

10 Advanced Excel Charting Examples

Click here to download the example Excel file How to use some tricks to create advanced charts in Excel. The following advanced charts are covered: Multi-Category Charts Gauge Chart Thermometer Chart Milestone Chart Water Fall Chart Gantt Chart Chart with Trend Arrows in Data Labels Actual Vs. Target Values Spotting Data in a Scatter Chart in Excel Dynamic Target Line in Excel Bar Charts Additional Charts in Excel Sparklines in Excel Step Chart in Excel Sales Funnel Chart in Excel Waffle Chart in Excel Pareto Chart in Excel Histogram in Excel

Dynamic Charts in Excel

Click here to download the example Excel file How to create dynamic charts in Excel. Dynamic charts allow you to change the chart based on user selection. For example, you can update the chart as soon as you change the scroll bar or click a button. Two detailed examples of creating dynamic charts from scratch Example 1 – Creating a dynamic chart that updates as soon as you use the scroll bar to change the year value. Example 2 – Creating a dynamic chart that plots the data on a scatter chart based on the column you select. There is a drop-down list that allows you to select which data series should be plotted on the X-axis and which should be plotted on the Y Axis Related Charts: Creating Dynamic Chart Range in Excel Dynamic Chart with Selection Box Dynamic Target Line in Bar Charts Creating Dynamic Chart Titles