Excel Mastery: Complete Course and Essential Hacks

The source provides an extensive transcript from a YouTube video offering a comprehensive Excel course designed for quick preparation and revision, particularly for job interviews. The tutorial begins with fundamental Excel concepts like rows, columns, and cells, before moving into essential functionalities such as data entry techniques, including the use of formatting and keyboard shortcuts. A significant portion of the content covers various data types and number formatting, followed by detailed explanations of advanced features like conditional formatting and data filtering. Finally, the video introduces a variety of critical Excel formulas, including RANK, IF, SUMIF, VLOOKUP, and XLOOKUP, alongside a promotional section about the Certified Management Accountant (CMA) certification from Zel Education.

Essential Excel Features and Formulas for Quick Reference

The Excel course content focuses on providing a quicket revision of essential features and formulas, often useful for interview preparation. The instruction emphasizes hand-picked features and formulas that are considered highly important.

The course covers content across several key categories, including basic terminology, data entry and formatting, fundamental formulas, and advanced features:

1. Excel Basics and Interface

The course begins by defining the core components of the Excel interface, noting that while often not asked in interviews, this knowledge is fundamental for using formulas.

• Sheets and Zoom: Demonstrates how to add new sheets using the plus sign and how to zoom in and out of the worksheet.
• Rows, Columns, and Cells: Defines columns (labeled A, B, C, etc.) and rows (labeled 1, 2, 3, etc.). A cell is the box formed by the intersection of row and column lines (e.g., C13), and understanding this relationship is crucial because formulas rely on it. The Name Box displays the name of the currently selected cell.
• Data vs. Formatting: Explains the distinction between raw Data (information) and Formatting (decoration or presentation).

2. Formatting, Data Entry, and Shortcuts

A significant portion of the course involves using shortcuts and formatting tools to improve data presentation and efficiency.

• Formatting Options: Formatting features are primarily found in the Home tab and include the Font, Alignment, and Number groups. Professional data presentation requires using appropriate fonts (like Aptos, Arial, or Calibri) and professional colors (such as blue, black, burgundy, dark green, or grey).
• Alignment: Demonstrates using Merge & Center to center headings across multiple columns.
• Shortcuts: The instructor heavily emphasizes using shortcut keys, noting that the Alt key activates the on-screen keys, allowing users to follow the path (e.g., Alt + H for Home tab) to execute commands. Specific shortcuts covered include:
• Merge & Center: Alt + H + M + C.
• Select All Data: Ctrl + A.
• All Borders: Alt + H + B + A.
• Thick Border: Alt + H + B + T.
• Fill Handle: Used to quickly generate series (like serial numbers).
• Data Entry Form Hack: Provides a hack to quickly enter data using a form interface via the shortcut Alt + D + O.
• Format Painter: Allows copying the formatting from one cell or range to another quickly.

3. Number Formats and Data Types

The content details how different types of data are handled in Excel, which is important for understanding calculations and presentation.

• Data Types: Discusses common formats, including Numbers (whole and decimal), Text, Percentage, Currency, and Accounting formats.
• Observation Tip: Text typically aligns to the left of the cell, while numbers align to the right.
• Date and Time: Covers Short Date (e.g., 16/9/2025) and Long Date (e.g., 16 September 2025). The course extensively explains Custom Date Formatting, where date components are represented by D (Day), M (Month), and Y (Year). The number of times the letter is repeated dictates the format (e.g., four D’s for the full day name).
• Decimal Management: Shows how to use the Increase/Decrease Decimal options.
• Fixing Errors: Explains that the “######” error indicates that the cell width is too small to display the number, which can be fixed by double-tapping between column headers.
• Date Shortcut: Provides the shortcut Control + Semicolon to insert the current date.

4. Data Arrangement and Visualization Features

The course introduces powerful features for analyzing and manipulating data sets.

Conditional Formatting and Filtering

The video contrasts Conditional Formatting and Filtering, noting they share similar options.

• Conditional Formatting: Applying formatting (colors, fonts, etc.) based on defined rules or conditions (e.g., coloring a cell green if the value is greater than 50%). Rules demonstrated include Greater Than, Less Than, Between, Equals To, Text That Contains, Date Occurring, and identifying Duplicate Values.
• Filtering: Allows users to display only a subset of the data (e.g., only data from Gujarat) by hiding non-matching entries. The shortcut for applying or removing filters is Ctrl + Shift + L.

Sorting and Series

• Sorting (Arrangement): Arranging data based on Text (A to Z), Numbers (Smallest to Largest), or Dates (Oldest to Newest). It also covers Sort by Color.
• Fill Series: A method to quickly generate long sequential lists (numbers or dates) by selecting the initial value, navigating to Fill > Series (Shortcut: Alt + H + F + I + S), specifying the column, and setting a stop value (e.g., 10,000).

Find and Replace

• The Find function (Shortcut: Ctrl + F) allows searching for specific text within the current sheet or the entire workbook.
• The Replace function is used to automatically substitute found text with new text across the selected area or workbook.

Advanced Features

• Flash Fill: Available after the 2019 version, Flash Fill recognizes patterns based on a single example provided by the user (e.g., combining names) and applies that pattern to the entire column. The shortcut is Ctrl + E.
• Table Creation: Converting a data range into a Table (Shortcut: Ctrl + T) provides dynamic features, including the automatic application of formatting and formulas to new entries, and enabling the use of Slicers for easy interactive filtering. Tables can be converted back to a normal range if needed.
• Pivot Table and Pivot Chart: The course shows how to create both a Pivot Table and a Pivot Chart simultaneously using the shortcut Alt + D + P. This allows users to summarize data, show values as ranks or percentages, and insert analytical tools like Slicers and Timelines.
• Data Validation (Dropdowns): Demonstrated as a method to create dropdown lists within cells, either based on an existing list or by manually entering values separated by commas.

5. Essential Formulas

The course covers several mandatory formulas, grouped by category:

CategoryFormulaDescription/Key RequirementBasic MathSUMBasic addition. Shortcut is Alt + Equals.CalculationPercentageCalculated using division; requires absolute cell reference (F4) to fix the denominator (total) when applied across multiple rows.StatisticalRANKCalculates the position of a number within a set of numbers; requires fixing the reference range using F4.ConditionalSUBTOTALUsed instead of SUM when working with filtered data, as it provides accurate totals only for the visible, filtered data. (Uses function number 9 for SUM).ConditionalSUMIFCalculates the sum of values based on a single specified criterion (e.g., total sales for one specific customer).LogicalIFApplies a conditional test; returns one value if the condition is True and another if False. Output text must be in double inverted commas.TextPROPERConverts text to sentence case (proper capitalization).Text/DateTEXTUsed primarily with dates to return the day (e.g., “Tuesday”) or month name based on format codes.DateDAYSCalculates the difference in days between two dates.DateEDATECalculates a future date by adding a specified number of months to a start date.LookupVLOOKUPRetrieves information from master data; cannot be used if the lookup value is duplicated. Requires column index number.LookupXLOOKUPAn alternative to VLOOKUP (available post-2019) that requires a lookup array and a return array, simplifying the process.

Excel Data Entry Fundamentals and Formatting

Data Entry basics in Excel involve a combination of accurately inputting information (Data) and subsequently improving its presentation (Formatting).

A data entry operator’s task is to transcribe available information (such as bills) into Excel and then apply necessary formatting.

Here is a detailed discussion of the fundamental aspects of Data Entry according to the course content:

1. Fundamental Terminology and Distinction

To perform data entry effectively, it is necessary to understand the basic elements of the Excel interface:

• Rows and Columns: Data is entered into cells defined by rows (labeled 1, 2, 3, etc.) and columns (labeled A, B, C, etc.).
• Cell: A cell is the box formed by the intersection of row and column lines. Understanding the row and column structure is crucial because it forms the basis of the cell name (e.g., C13) and is the backbone for formulas. The Name Box displays the name of the currently selected cell.
• Data vs. Formatting:Data refers to the raw information itself.
• Formatting is the process of decorating or presenting the data (e.g., applying colors, fonts, borders, or alignment). Proper representation of data always requires formatting. Formatting options are generally found in the Home tab, specifically in the Font, Alignment, and Number groups.

2. Setting Up the Data Entry Table

The physical act of data entry begins with structuring the worksheet:

1. Headings: Data entry requires setting up appropriate headings (e.g., Serial Number, Party Name, Amount).
2. Merging Headings: To center a main heading (like “XYZ Limited”) across multiple columns, the Merge & Center feature is used. Using the shortcut key for this process is recommended.

• Merge & Center Shortcut: Alt + H + M + C. This shortcut is derived by pressing Alt (the activation key), followed by H (for the Home tab), M (for Merge), and C (for Center).

1. Data Input and Series Generation: After setting up headings, entries are written sequentially. For sequential numbers (like serial numbers), the Fill Handle can be used. By selecting the first two numbers (e.g., 1 and 2) and dragging the fill handle, the rest of the numbers in the series can be automatically generated.

3. Applying Borders and Enhancing Presentation

Once the data is entered, formatting is applied for professional presentation:

• Selecting Data: The shortcut Ctrl + A (Control + All) is used to select the complete dataset.
• Applying Borders:All Borders: To apply borders to every cell within the selected data, the shortcut is Alt + H + B + A.
• Thick Border: To apply a darker, thicker border around the outside of the data range, the shortcut is Alt + H + B + T.
• Color and Font:Filling Color: The “bucket” tool is used to fill entire cells with color. When choosing colors, use darker shades for higher elements and complementary lighter shades below (e.g., dark blue contrasts well with light grey).
• Font Color: The ‘A’ symbol is used to change the font color.
• Professional Fonts: It is recommended to use professional, simple fonts such as Aptos, Arial, or Calibri.
• Professional Colors: Recommended professional colors include blue, black, burgundy, dark green, and grey.
• Bold/Italic: The Bold option can be used to make text thicker, often used for headings.

4. Advanced Data Entry Method (The Form Hack)

The course highlights a rapid data entry method using a built-in form interface:

• Form Shortcut: To enter data using a form, select the data range and press Alt + D + O.
• This method allows continuous entry without the need to apply macros or manual setup. New entries are generated by navigating to “New” and pressing Enter after inputting the information.

5. Data Type Observation

While entering data, a basic observation can distinguish between text and numbers:

• Text vs. Numbers: Text generally aligns to the left side of the cell, whereas numbers align to the right side.

Essential Excel Formulas and Functions Reference

The course content provides a quick revision of essential formulas and functions, emphasizing those that are most important and often asked about in interviews. Formulas are considered the backbone of Excel.

The formulas and functions discussed fall into several categories, including basic mathematics, statistical calculations, conditional logic, text manipulation, date calculations, and lookup functions.

1. Basic Calculation and Statistical Formulas

These formulas handle fundamental mathematical and ranking operations:

FormulaDescriptionKey RequirementSUMCalculates basic addition.A shortcut is available: Alt + Equals (=), which automatically takes the complete range above the current cell.PercentageCalculated using division, as there is no dedicated percentage function.Requires taking the student’s total marks (numerator) divided by the grand total (denominator, e.g., 400). If the denominator cell is used, it must be fixed using F4 so that the reference does not change when the formula is copied down.RANKDetermines the position of a number within a set of numbers.Requires two inputs: the number to be checked (e.g., a student’s percentage) and the complete reference range of all numbers (e.g., all student percentages). The reference range must be fixed using F4. Users must also choose between descending or ascending order.SUBTOTALCalculates totals (like SUM) but is specifically designed for use with filtered data.Unlike the standard SUM formula, SUBTOTAL provides accurate totals only for the currently visible, filtered data. When using SUBTOTAL, you input the function number (e.g., 9 for SUM) followed by the reference range. This is necessary because the normal SUM formula will show the total of the entire dataset, even if a filter is applied.SUMIFCalculates the sum of values based on a ** single specified criterion**.Requires providing a range (where to look for the criterion), the criteria itself (what to look for, e.g., a specific party name like “Swift Nova”), and the sum range (the column containing the values to be summed). All ranges should typically be fixed using F4.2. Logical and Conditional Formulas

The IF function is considered “universal” and mandatory for any complex work involving dashboards or sheets.

FormulaDescriptionKey RequirementsIFApplies a conditional test and returns one value if the condition is True, and another if False.Logical Test: The condition to be checked (e.g., is the percentage greater than 50%?). Value if True/False: The outputs if the condition is met or not met. Any text output (like “Pass” or “Fail”) must be enclosed in double inverted commas.3. Text and Date Formulas

These formulas assist in reformatting text and performing time-based calculations:

FormulaDescriptionKey RequirementsPROPERConverts text into sentence case (proper capitalization).Requires selecting the text cell. This is useful for cleaning up data where names or phrases might be entered in all small or all capital letters.TEXTPrimarily used with dates to extract specific components like the full day or month name.Requires the value (the date cell) and the format (the code defining what to extract, enclosed in double inverted commas). For example, typing “DDDD” will return the full day name (e.g., “Tuesday”).DAYSCalculates the difference in the number of days between two specified dates.The syntax requires providing the end date first, followed by the start date.EDATECalculates a future due date by adding a specified number of months to a starting date.Requires the start date and the number of months to add.4. Lookup Formulas

Lookup formulas are crucial for retrieving information from a master dataset into a report or summary.

FormulaDescriptionKey RequirementsVLOOKUPRetrieves information from a table based on a lookup value.Crucially, the lookup value (the item being searched for, e.g., “Bharat Innovation”) cannot be duplicated in the master data. Requires specifying the lookup value, the complete table array (the data range, often excluding headers), and the column index number (the number of the column containing the desired answer). For an exact match, the final argument should be set to FALSE.XLOOKUPAn alternative to VLOOKUP and HLOOKUP, available in Excel versions after 2019.It is considered much easier to use. It requires the lookup value, the lookup array (just the column where the lookup value is found), and the return array (just the column where the desired answer is located). Similar to VLOOKUP, the lookup value should not be duplicated.5. Features Related to Formulas

In addition to formulas, the course touches on features that automate pattern recognition and data manipulation:

• Flash Fill (Control + E): This feature, available after the 2019 version, works based on pattern recognition. If the source data and the desired output follow a similar pattern (e.g., combining first and last names), the user provides one example answer, and Flash Fill automatically generates the rest of the output for the entire column.
• Dynamic Tables: Converting data to a Table (Shortcut: Ctrl + T) makes formulas dynamic, meaning they automatically extend and apply to new entries added to the table.

Excel Conditional Formatting and Rules

Conditional Formatting is a vital feature in Excel that allows you to apply formatting (decoration) to cells based on specified conditions or rules. The name itselfConditional Formatting is a vital feature in Excel that allows you to apply formatting (decoration) to cells based on specified conditions or rules. The name itself explains its function: you are applying formatting using a condition.

Conditional Formatting and Filtering share similar options, although they serve different purposes. The rules for Conditional Formatting are primarily found under the Home tab.

Purpose and Mechanism

Conditional Formatting means applying formatting—such as colors, fonts, borders, alignment, or number format—with a condition.

For example, you might set a rule: “If the cell’s amount is greater than 100, color it green; otherwise, color it red”. The formula acts like an “If” condition, where if a criterion is met, the decoration is applied.

Key Rules and Conditions

The course content demonstrates several essential rules found within the Conditional Formatting feature:

1. Greater Than / Less Than: You can highlight values that are above or below a specified number.

• Example: Highlighting quantities greater than 10 with red color.
• Example: Highlighting quantities less than 7 with green color.

1. Between: This rule highlights values that fall within a defined range (e.g., between 10 and 15).
2. Equals To: This highlights cells containing a specific, exact value (e.g., 20).

• When using Equals To, you can apply a Custom Format, allowing you to choose specific fill colors (e.g., blue) and font colors (e.g., white and bold) that are not available in the preset options.

1. Text That Contains: This highlights cells where the text includes a specific string.

• Example: If you select a column and set the rule to highlight cells containing “प्रदेश,” it will highlight “Uttar Pradesh,” “Madhya Pradesh,” and “Himachal Pradesh” because they all contain the specified text.

1. Date Occurring: This allows you to highlight dates based on their relationship to the current date, such as Yesterday, Tomorrow, or Today.
2. Duplicate Values: This feature quickly identifies and highlights any values that are repeated within the selected range.
3. Top/Bottom Rules: You can highlight the Top 10 items or Top 10 Percentage of values in the selection.
4. Data Bars, Color Scales, and Icon Sets: Beyond highlighting text or numbers, Conditional Formatting offers graphical visualization options like Data Bars, Color Scales, and Icon Sets.

Managing Conditional Formatting Rules

Rules can be cleared or managed in two ways:

1. Clear Rules from Selected Cells: Removes formatting only from the specific area you have selected.
2. Clear Rules from Entire Sheet: Removes all Conditional Formatting rules applied across the entire worksheet.
3. Manage Rules: Used to view or edit existing rules, such as correcting an incorrect range selection.

Distinction from Filtering

While Conditional Formatting options are very similar to those found in Filter dropdowns (e.g., Greater Than, Text That Contains, Date Occurring), their core difference lies in how they display the data:

FeatureConditional FormattingFilteringData DisplayAll data remains visible (e.g., 10,000 entries).Only the matching subset of data is displayed (e.g., 2,000 entries).HighlightingThe results that meet the condition are highlighted with color.The results that do not meet the condition are hidden (data is not deleted).Conditional Formatting is preferred when you want the complete dataset to remain visible, but certain data points need to be visually highlighted.

Excel Data Validation: Creating Dropdown Lists

Data Validation is a feature in Excel primarily used to create dropdown menus within cells. It offers a way to restrict or guide the type of data that can be entered into a cell or range, thereby ensuring consistency and ease of data entry.

The course content demonstrates two primary methods for setting up dropdowns using Data Validation:

1. Creating Dropdowns from an Existing List (Source List)

If you already have a set of unique values prepared in a range (such as unique party names or categories), you can use this range as the source for your dropdown list.

1. Process:Select the cell(s) where you want the dropdown to appear.
2. Navigate to the Data tab.
3. Go to Data Validation.
4. In the Data Validation window, under the “Allow” setting, choose List.
5. In the “Source” field, select the range of cells containing the unique values (the pre-existing list).
6. Click Enter or OK.

• Result: The selected cells will now have a dropdown arrow, allowing users to select any value from the source list. This can be dragged down to apply the validation to more cells.

2. Creating Dropdowns by Manually Entering Values

If the list of possible entries is small or static, you can manually type the options directly into the Data Validation source box.

1. Process:Select the cell(s).
2. Go to Data > Data Validation.
3. Under the “Allow” setting, choose List.
4. In the “Source” field, manually enter the desired values, ensuring they are separated by commas.

• Example: To create a dropdown for typical survey responses, you would enter Yes, No, I don’t know.

1. Click OK.

• Result: The dropdown will contain only the options you typed.

Other Data Validation Applications

The Data Validation feature is capable of more than just creating dropdowns. It can be used to set restrictions on data entry.

• The options available within Data Validation (such as Number, Decimal, etc.) are similar to those found in Conditional Formatting.
• While the course primarily uses Data Validation to teach the creation of dropdowns, it is noted that this feature can also be used for other types of data restriction.