A Drop of Excel - all you need to know for day-to-day use

Let’s start with understanding Excel’s user interface to get used to the different menu items and options on the screen. We will then dive into Cell referencing basics where we will understand how a cell reference, range, column selection, row selection is done.

Don't forget to download the use the Excel file in the Resources here.

# Related Videos/Posts

• Limitations in Excel’s rows/columns/sheets -- https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

• 18 Most Useful Excel shortcuts -- https://youtu.be/89dM9-QA2AY

Understand the dataset first that we will use in all examples, so that with each video, you can relate to the data very well and learn better.

Now that you’re comfortable with the user interface and cell referencing basics in Excel, let’s move to learn the 3 types of data and how data must be structured in Excel.

It is very important to understand the 3 types of data - numbers, text, date&time. These are the fundamentals without which learning formulas and functions in Excel will not be any fun.

Once you have understood how to structure your data in Excel, using various features like Sort, Filter, Pivot tables and even various functions, becomes very easy.

Before we begin learning formulas and functions in the next video, let us learn about Data and Cell formatting. You will learn about

• How to quickly format data in many different ways using Format Cells

• How to use format painter in Excel

• How to repeat the last action in Excel with a shortcut

• How to merge cells

• How to wrap text

• How to clear all formatting in one shot in Excel

• How to work with rows, columns and sheets

Let us now learn about,

• Difference between formula and function in Excel

• How to create a formula

• What are the parts of a function in Excel

• SUM, MAX, MIN, AVERAGE, COUNT, COUNTA, COUNTBLANK, ROUND, CEILING, FLOOR functions

• Measure versus Calculated column

Many times you have data in multiple sheets instead of only 1 sheet. You want to do some calculations based on data in these multiple sheets.

This is where you will see the need to create formulas across sheets in Excel. Sometimes you may also need to carry out calculations on data across multiple Excel files.

In this video, we will learn both of them, in a very simplified explanation.

Dates in Excel seem to be a very troublesome area for many people. Some feel there are bugs in Excel, some just keep seeing errors while working with Dates. Let’s clear all these issues and understand how to very easily use dates in Excel.

We will learn the following in the video,

• Understand how a date should be so that Sort, Filter, Pivot, everything works properly.

• Shortcut to format dates very quickly.

• Add and subtract days from a date.

• Get day’s name from a date.

• Get month’s name from a date.

• Today() function versus shortcut to get today’s date.

• Use of Today() function.

Working with a lot of data and numbers in Excel can be intimidating sometimes. If you learn the following features and tricks in Excel, it will become very easy for you to manage large data sets in Excel,

• How to split data 1 column to multiple columns

• Text to columns

• Sort on single column and even on multiple columns

• Filter data quickly to see desired data only

• Remove Duplicates or get a Unique list of values in no time

• Freeze Panes

• Printing tips and tricks in Excel

In terms of applying restriction and protection, let us learn the most essentials that you must know in Excel,

• How to make a dropdown in Excel

• How to allow data entry only in certain cells in Excel

• How to allow only numbers greater than 0 or 1000 or any number

• How to lock Excel file with a password

Conditions are very important to understand and use in Excel. It has the power to save you hours and hours of time by automating your calculations.

For example, You have to give a value of 0 incentive to any order upto the value of 5,000 and 10% incentive to any order above the value of 5,000.

You have 17,000 orders data. It would take you a very long time to do this manually. Even if you use sort and filter, it will take longer and also for any new orders coming in, the process will need to be repeated.

Using IF condition simplifies this.

Conditional formatting on the other hand can highlight all orders where the incentive is greater than 10,000. This will give you a quick visual indication of higher incentive values. This also if you do it manually, can take a very long time to complete.

Let us today learn about,

Paste special

• Paste with same column width as source

• Paste as values

• Transpose in Excel

Find & Replace

• Replace in whole sheet

• Replace in selected cells only

• Replace with a space

• Replace with a blank

Go To Special

• Blanks

• Formulas

Vlookup seems very confusing and you may often forget it even if you have learnt it earlier.

We will here learn Vlookup in a very simplified manner that you will not forget.

$ referencing is also a must to learn to understand how to make cell references constant in formulas.

Xlookup has been recently released by Excel and is much easier to use even than Vlookup!

We will also be learning about IfError with Vlookup, which is made a part of the new Xlookup.

~~ To Learn Pivot Tables in Depth ~~

MASTER BUSINESS REPORTING USING PIVOT TABLES & CHARTS

https://www.udemy.com/course/master-business-reporting/?couponCode=AA4E458F885D80E2ED11