The essential skills that a competent business Excel user should be aware of are Formatting & basic formulas, Excel shortcuts, VLOOKUPS & conditional statements, Data analysis like filtering, sorting, and analyzing data, Data visualization like tables, charts, and dual axis charts, What-if-analysis and Pivot tables.
The complete Excel course is bifurcated into three major parts viz. Beginner Excel Course, Intermediate Excel Course, and Advanced Excel course. This structure of the Excel course is explained in detail below.
Things you should learn in BEGINNER EXCEL COURSE
In the Beginner Excel course, you will learn the Basic Excel Skills that are helpful for the new bees. From entering data in a spreadsheet to creating formulas and functions, you will get to know all the basic things about Excel in the Excel Beginner course. We have listed below the Excel features that are covered under the Excel Beginner course.
Conditional formatting is a feature of Excel. In conditional formatting, you can apply a format to a cell or a range of cells based on certain norms. You can highlight cells with a certain color, depending on the cell’s value.
You can change a range of cells into an Excel table to manage and analyze a group of linked data easier. You cannot generate or insert tables in a shared workbook. You can create a table by clicking on the home tab and selecting ‘Format as Table’ option.
You can add shapes like boxes, circles, and arrows, to your documents, email messages, slide shows, and spreadsheets. To add a shape to your document, you can click on Insert tab and click Shapes.
When you add one or more shape, you can add text, bullets, and even numbering to them. You can also change their fill, outline, and other effects on the Format tab.
You can format numbers easily in Excel when you type a number in a cell. Most of the times, numbers that are formatted with the General format are displayed as it is you type them. Nevertheless, if the cell is not widespread enough to show the complete number, the General number format rounds the numbers with decimals. You can also use other number formatting options. In this course, you will get to learn all the number formatting options and their uses.
Adding the top/bottom/left/right borders for a selected cell in Excel is an easy task. But, if you want to custom the border thickness, width, color, or add diagonals, it sounds difficult. In this course, you will learn how to design custom border style with customizing thickness, width, color, or diagonal in Excel, and save custom border style in Excel as well. Also, you will learn how to add a custom border for selected cells with special border thickness, width, color, or even add diagonal in Excel easily.
ADJUSTING COLUMN WIDTH AND ROW HEIGHT
You can easily expand or reduce Excel’s column widths and row heights when you take this course. This course teaches you several ways to adjust columns width and rows height.
While working in Page Layout view, you can specify a column width or row height in inches, centimeters or in millimeters. The measurement unit for page layout view is in inches by default. You can also change to the Normal view, but then the column widths and row heights will be displayed in points.
You can vary the font size for the entire text or just the selected text in Excel. In this course, you will learn to use the new default font size or font in Excel. Also, you will learn to use the new default font and size in existing workbooks and to move worksheets from an existing workbook to a new workbook.
RELATIVE AND ABSOLUTE CELL REFERENCE
The two types of cell references in Excel are Relative and Absolute. Relative and Absolute references work differently when copied and pasted to other cells. Relative references get transformed when a formula is copied to another cell. On the contrary, Absolute references remain constant, even after they are copied. In this course, you will get to learn everything about Relative and Absolute Cell Reference.
CALCULATING THE PERCENTAGE CHANGE
In this course, you will come to know how to calculate the percentage change in Excel. You can select a blank cell for locating the calculated percentage change, then you can enter the formula into the Formula Bar, and press the Enter key. You can keep selecting the resulting cell, then click the Percent Style button in the Number group under the Home tab to format the cell as a percentage. In this course, you will get in-depth knowledge about calculating percentage change.
CALCULATING YTD PROFITS
Year to Date (YTD) denotes the period from the beginning of the current year to a definite date before the year-end. It means that YTD depends on the number of days from the beginning of the calendar year up to a specified date. You can become an expert in calculating YTD profits if you take this course. This course is very beneficial for accounting and finance and financial reporting.
CREATING FORMULAS AND FUNCTIONS
An Excel formula is an expression that calculates the value of a cell. While Excel Functions are predefined formulas and they are already existing in Excel. In this course, you will not only get to learn the meaning of Excel formulas and functions but also you will learn how to create them. You will also learn all the categories of formulas and functions like arithmetic functions, logical functions, etc. in this course.
USING FORMULAS FOR ARITHMETIC TASKS
In this course, you will study how to use formulas for carrying out arithmetic operations. You can add, subtract, multiply and divide numbers in a cell using the arithmetic formulas. You can use any of the arithmetic operators for doing this task. You cannot use logical operators like & for carrying out arithmetic tasks.
You can create reusable formulas in Excel without repeatedly typing them every time you use them. In this course, you will learn how to use the AutoText utility in Excel for reusing formulas. This will smartly make formulas reusable by creating the formula as Auto Texts in Excel.
USING RANK FUNCTION
Using the RANK function in Excel you can rank the values with the help of a simple function to dynamically calculate the rank. If you change any value, you’ll get a new rank automatically. In this course, you will learn how to calculate the rank using rank function easily. Also, you will learn the applications and uses of this function.
USING THE STD FUNCTION
Standard Deviation is a statistical measure, which identifies the amount that a set of values vary from the average value.
To select the range of values containing the data, you can simply use the Excel STDEV Formula. This is used to calculate the sample standard deviation.
In this course you will get to learn everything about STD function like its uses, advantages, working and its applications in different fields.
SMALL AND LARGE FUNCTIONS
The Excel LARGE function returns numeric values depending on their position in a list after sorted by value. While the Excel SMALL function returns numeric values based on their position in a list ranked by value. This means that it can retrieve “nth smallest” values i.e. the smallest value, the 2nd smallest value, the 3rd smallest value, etc.
In this course, you will get to learn the small and large functions of Excel in detail.
The MEDIAN function of Excel returns the median of the numbers provided. This function is a built-in function in Excel that falls under the category of the Statistical Function. You can use it as a worksheet function in Excel. You can learn the operation and uses of the Median function of Excel in this course.
The COUNT function in Excel counts the number of figures in supplied values. Values can be anything like items, cell references, or ranges. Count function can handle up to 255 additional values. You can learn more about the count function in this course.
Things you should learn in INTERMEDIATE EXCEL COURSE
Microsoft Excel Intermediate Course or training covers the Excel formulas that are mandatory to be known in all finance jobs. This course is very useful for freshers looking for a career in the finance sector. It includes the relevant and essential information that you will need to know at work. Before going for this course, you must have basic Excel knowledge. Excel Intermediate course will help you to learn all formulas that are required in any finance and accounting jobs.
Many Excel users find that using an external keyboard with keyboard shortcuts for Excel helps them to work more professionally. For those users who are having mobility or vision disabilities, keyboard shortcuts can be convenient rather than using the touchscreen. Excel shortcuts are an excellent alternative to a mouse. Therefore, this course has introduced the Excel keyboard shortcuts for Windows. The shortcuts in this course refer to the US keyboard layout.
The VLOOKUP function is an Excel function that looks up and retrieves data from a specific column in a table. The “V” in VLOOKUP stands for “vertical”. Lookup values appear in the first column of the table, with lookup columns to the right. VLOOKUP is a built-in Excel function that is designed to work with data that is organized into columns. Many people get confused and stuck in between while carrying out the VLOOKUP function. This course helps to understand the concept of VLOOKUP and helps to carry out the function easily and smoothly.
The HLOOKUP function in Excel performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index number. The HLOOKUP function is a built-in function in Excel that is categorized as a Lookup Function. The V in the VLOOKUP stands for vertical search while the H in HLOOKUP stands for horizontal search. This course helps to understand the concept of HLOOKUP and helps to carry out the operations using this function very easily.
The INDEX function in Excel returns a value in a table based on the junction of a row and column position inside that table. You can use the INDEX function as a worksheet function in Excel. You can enter the INDEX function as part of a formula in a cell of a worksheet. This course will help you to understand the concept of INDEX function and will make you familiar with all its uses.
The MATCH function in Excel helps you to search for a value in an array and return the relative position of that item. The MATCH function is a built-in function in Excel that comes under the category of the Lookup Function. You can use this function as a worksheet function in Excel. In this course, you will find it easy to understand the working of MATCH function easily.
IF AND NESTED IF FUNCTION
The IF function in Excel is one of the most widespread functions in Excel, and it helps you to make logical comparisons between a value and a value that you expect. When you have several numbers of conditions to fulfill then the IF function in Excel can be nested. In this course, you will learn how to use the IF and nested IF function wisely.
The DATE function in Excel returns the serial date value for a date. The DATE function is a built-in function in Excel that comes under the category of a Date/ Time Function. In this course, you will get to know the applications of DATE function in different fields and the tricks of using it.
TEXT TO COLUMN
In Excel many of the times you will find a delimited text that needs to be separated. Delimited text is a text that includes characters such as a comma, tab, or space. To separate the delimited text into different columns, you can highlight the range of text to be separated. You can use the text to column feature to separate the delimited text. In this course, you will learn the importance of Text to column feature. Also, you will learn its ways of using and its applications.
HOW TO CREATE A PIVOT TABLE
Sometimes you have huge data that makes it difficult to analyze all of the information in your worksheet. You can overcome this problem by using PivotTables. They make your worksheets more manageable by briefing data and allowing you to operate it in different ways. This course will teach you everything about Pivot tables. From creating Pivot tables to analyzing it and creating charts, you will become an expert in Pivot tables after taking up this course.
The 2D lookup uses the INDEX and MATCH function instead of using the VLOOKUP function. This is because the MATCH function returns the position of a value in a given range and the INDEX function returns a specific value in a one-dimensional range. Therefore, the combination of these two functions is called 2D lookup function. You can use the INDEX and MATCH function to perform the advanced lookups as the INDEX function can return a specific value in a two-dimensional range. This course will teach you the working and uses of 2D LOOKUP function in detail.
The HYPERLINK function in Excel forms a shortcut to a file or web address. The HYPERLINK function is a built-in function in Excel that comes in the category of a Lookup/Reference Function. You can use this function as a worksheet function in Excel. In this course, you will learn how to hyperlink the links in the text in Excel using the HYPERLINK function.
You can sort the data range horizontally according to row in Excel. Which means that you can reorder the columns using horizontal sort from left to right. Therefore, you can organize a data range from largest to smallest or from smallest to largest and even from left to right or right to left. In this course you will learn to use this Horizontal sort feature smoothly and also you will get to learn about the situations where the horizontal sort is required.
Things you should learn in ADVANCED EXCEL COURSE
Advanced Excel training will help you get your job done very quickly and easily. The tasks that would take many hours to complete will now get completed within a few minutes using Excel formulas, macros, and other Excel tricks. Advanced Excel users are aware of VBA & can write macros to automate their day to day work, therefore able to save countless hours of time & money.
ADVANCED FIND AND REPLACE FOR EXCEL
Advanced Find and Replace for Excel features make it easy to search across multiple spreadsheets and workbooks, select or replace all found values in a click. It can concurrently search in values, formulas, hyperlinks, and comments with a case-sensitive, case-insensitive or exact match. Also, it can check your worksheets for typos. You can learn in detail about Advanced Find and Replace feature in this course with examples.
VBA an abbreviation for Visual Basic for Applications. VBA is the programming language of Excel and other Office applications. Using Excel VBA, you can program the tasks in Excel by writing macros. This course covers the complete Excel VBA syllabus. You will become an expert in VBA after taking this course.
COUNT IF AND COUNT A
The Excel COUNTIF function is used to count cells that meet a single-criteria. The COUNTIF function can be used to count cells with dates, numbers, and text that match certain criteria. It supports logical operators and wildcards for partial matching.
The Excel COUNTA function returns the count of cells that consists of numbers, text, logical values, error values, and empty text. You cannot count empty cells using the Excel COUNTA function. In this course, you will get to learn the functioning of both COUNTIF and COUNTA function in depth.
CHARTS IN EXCEL
Charts in Excel are used to represent the tabular information into graphics. As you’ll see in this course, creating charts is very simple. This course explains all types of charts available in Excel. You can also learn which kind of chart is used to display what information.
An Excel dashboard is one pager that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on it. It contains charts/tables/views that are backed by data. A dashboard is usually called a report, but all the reports cannot be dashboards. With the help of this course, you will learn to create the dashboard in Excel very easily.
The fuzzy lookup is used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables. In this course, you will get to learn everything about this unique function of Excel.