Master Microsoft Excel - Excel from Beginner to VBA Expert

In this lesson we will start a new file from scratch and create First excel file by entering data and create a table.

Making information look more attractive in Excel and Format cells by selecting fonts and color fills.

This guide will show you how to use the alignment tool as well as the Wrap Text tool to create a well-formatted spreadsheet.

If you are dealing with a data-heavy spreadsheet, sometimes we don't want to see all the information and need to hide or unhide rows and columns to better see the information. Fortunately, Excel makes it easy to do this.

In this lesson, we will work with basic mathematics in Excel and utilize the mathematics including multiplication and division in Excel.

In this lesson, we will learn the anatomy of Excel formulas and understand how to build them.

Learn about working with absolute & relative cell referencing and techniques for copying formulas to ensure that formulas are targeted at the intended cells when you copy them from one location to another.

Use to understand the Anatomy of Excel functions and what their components stands for.

Learn Basic math functions such as SUM, ROUND and SUBTOTAL.

Learn Basic Statistical functions i.e. COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.

Learn how to build Excel's logical functions such as IF, AND, OR and make them more complex by nesting AND and OR within them.

Excel has many functions to offer when it comes to manipulating text strings. In this lesson, we will learn to break apart text with the LEFT, RIGHT, MID, FIND and SEARCH Functions and to combine text with the & character.

Learn to use date and time functions in order to display today's date and current time while using the Excel. Understand how time works in excel, how to change from whole numbers into time and increments and back again.

Learn to work with Formula Auditing Tools including tracing precedent and dependent cells and formula evaluation.

Learn to use SUMIF, COUNTIF and and SUMPRODUCT to add cells only when certain conditions are met.

Learn about creating and updating external links and about the potential dangers of external links in excel.

Learn to work with EOMONTH and EDATE functions that easily shift date from one month and year to another.

Using VLOOKUP with an approximate match is ideal for tables that have gaps in data, such as volume pricing or tax table. Learn to use Vlookup to find an approximate match and return it or the corresponding value from another column.

Learn to use Vlookup to find an exact match and return it or the corresponding value from another column. Using VLOOKUP with an exact match is perfect to look up customer data, product numbers or other data where there is a unique key to find in a data table.

Learn to use VLOOKUP with Large Tables. It is limited to the number of columns that your version of excel provides (65536 rows in pre 2007, 1048576 rows in 2007 and above).

Learn to use the nested lookup function. Sometimes we need to derive the result based on two different ranges and here we will learn to use multiple VLOOKUPS in one formula.

Working with INDEX, MATCH and HLOOKUP as alternatives to the VLOOKUP function. INDEX and MATCH can be used in combination to create truly dynamic lookup methods, whereas HLOOKUP is best suited to horizontal data than VLOOKUP. INDEX-MATCH is more robust and faster, but is a lot easier to learn for those who have mastered VLOOKUP than those coming to INDEX-MATCH without background knowledge.

Learn to use Sorting data in Excel by a single column or multiple columns, create our own custom sorting. You can refer to the ones that are built-in already but it is certainly necessary at times to use this feature. Knowledge of how to use Excel’s sorting feature can be essential for presenting your data in an easy to read format.

Filtering can be very useful for drilling down into data, finding only records that have a specific relevance to the situation you are investigating. Learn to use standard filtering in excel by specific words, dates, and applying multiple filters to a single data table. In addition to standard filter, the Advanced Filter is being used for those kinds of situations that cannot be met by the standard filter.

Creating Named Ranges and their benefits. The benefit of naming ranges are that it gives us a quick way to select individual or groups of important cells. Allows us to easily identify which cells are used in a formula, Pivot Table, or chart and can make our formulas easier to read.

Creating Excel tables and their benefits. Instant access to alternate row shading, Auto-expansion when new rows or columns are added, Auto-complete of identical formulas down the entire column. Excel tables are the standard and best way to organize tabular data in Excel today.

Learn to record basic Excel macros to automate your actions instead of doing them manually. Excel contains a powerful language called VBA that can be used to script actions to happen. Recording macros is the first step in this process and is very useful for automating repetitive steps.

PivotTables are user-friendly and amazing tool for creating instant data. Pivot Table makes data analysis easier and here we will learn to create and modify basic PivotTables (for quickly summarizing and highlighting data). Pivot Tables are an amazing tool that can summarize, slice and dice data in a variety of formats. While they shine with large data sets, they are equally powerful with small data sets as well.

Learn to control formatting and other options in PivotTables. Having your data in tabular format is good but we need to format it to present it in the most readable and approachable way.

Learn to create and modify basic calculated fields for Pivot Tables in Excel. Calculated fields are used to build mathematical and logical formulas in a Pivot Table rather than relying on adding those calculations to the data source. This is very useful, as there are situations where we can't modify our source data but need to add extra calculations to our Pivot Tables.

Learn to create interactive dashboards in Excel and visually explore your data. If you want greater control over a Pivot Table when you are making presentation or simply analyzing data then you must know about Slicers which is a relatively new feature in Pivot Tables introduced in Excel 2010 or later versions.

Learn to use Grouping the data by Date, time and other factors in excel. Worksheets with a lot of complex and detailed information are difficult to read and analyze but in this lesson we will learn how to group the data of different dates in months, times and other factors in just few keystrokes.

Learn to create Pivot Chart in excel. Pivot Chart is a chart made on the Pivot Table of any data. Pivot Tables and Pivot Charts are related to each other and is a pre-defined feature in excel.

Learn to perform what-if analysis in Excel using the Scenario Manager, Goal-seek and Data Analysis Tools. These tools work very well when you either want to try seeing how things would look with different variables in place or when you need to work out what variables will drive the outcome you are looking for. Sometimes a formula depends on multiple inputs and you want to see how different inputs values would impact the result. The data table is perfect for that situation.

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Solver comes in use where we need to find an optimal (maximum or minimum) value for a formula in one cell based on multiple constraints or limits on a worksheet. Solver uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Preparing a report is one thing but if it looks ugly, no one want to engage with it. The format cells dialog contains the abilities to apply more advanced formatting than the Ribbon’s shortcuts in order to make your reports truly grab your audience.

Adding and working with Excel’s outlining tools to quickly hide irrelevant sections of your document. Outlining tools are fantastic to compress pieces you don’t need in order to focus on the data that you wanna see.

Custom number formats control how numbers are look in Excel. The key benefit is that they change how a number looks without changing it's original data. Building your own custom number formats to display dates, values and variances the way you want to see them.

Learn to create top/bottom and highlight cell rules as well as applying icon sets and data bars. It is used to highlight key pieces of data that meets certain conditions and reacts dynamically,updating when the data is changed.

Learn to create custom conditional formatting rules based on formulas. Conditional Formatting of Excel’s defaults won’t cover every scenario we need but fortunately we have the ability to build our own rules to set.

Learn to create an effective column chart by removing distracting elements from your data whenever you are charting. Doing this will help you convey your message to your readers as quickly as possible.

Learn to create an effective bar chart by removing distracting elements from your data whenever you are charting. Doing this will help you convey your message to your readers as quickly as possible.

Learn how to build an effective pie chart when you should and shouldn’t use them. When you need to convey a simple percentage value or a maximum of 3 data series that need to be displayed as a proportion, pie chart is best suitable chart for such type of data and If there are more than 3 data series, prefer bar or column chart instead.

Learn to create an effective line chart through manipulation of chart elements to enhance its story telling ability, Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis which will help you to convey the message to your readers as quickly as possible.

Learn to create Waterfall Chart to see stock movement.

In this lesson, we will look at sparklines: What they are and how to create and modify them.

Learn to create combined chart in excel when you want to graph 3 line series but only want 2 in focus most of the time to convey your message to your readers as quickly as possible.

Combine Column and Line charts together to make a compelling chart.

An overview of unpivoting data in a Table. There are various ways to accomplish any Excel task but if you work with PivotTables, you know that they put information out the way users want to read it but you can't use it to drive another PivotTable. So what if there was an easy way to unpivot the data? There is and it is called Power Query!

Learn to create reshape data into a refreshable contiguous data set using Power Query. Often when you import data from another application, you will find that it contains blank or garbage rows. Since Excel’s most powerful tools require contiguous ranges to work with, we need a way to quickly convert our data to this format. Therefore, Power Query is the best tool for consolidating your data in vertically contiguous (without blank rows or other garbage) – for Tables, PivotTables and Charts.

An overview of importing and cleaning data from a text file using Power Query. This feature is being used when you use a text file as a data source for your query and must need to clean up the (often) messy results so the data can be loaded into Excel.

An overview of manually appending multiple tables using Power Query. This feature can be used when you want to stack multiple tables one after the other into one single table instead of copying and pasting the data to compile it. It is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables.

Importing Data from an Text File into an Excel Table.

Create Excel Tables from using Power Query.

In this lesson, we will learn how import data from an Excel range.

In this lesson, we will show you how you can build a solution that pulls its data from a different Excel Workbook.