Mastering Data Visualization & Analytics with MS Excel

Explore the basics of Excel workbooks and worksheets, including adding, deleting, moving, and renaming sheets, and understand cells, columns, and rows with their vast capacity (over 17 billion cells).

Master five methods to navigate adjacent cells in Excel after data entry, using the forward arrow or tab to move forward, shift+tab to move back, and shift+enter to move up.

Navigate within an Excel table using ctrl plus arrow keys to move from the first to the last data cell, such as N30.

Master selecting cells in Excel by using arrow keys to choose individuals, then use control and shift to extend selection to multiple cells, whole columns, and the entire table.

Learn to select the entire table in Excel using two methods: shift plus control and control plus a, and observe the active cell highlight.

Learn practical Excel selection shortcuts to instantly select entire rows or columns, non-contiguous ranges with Ctrl, and contiguous ranges with Shift, boosting data visualization workflows.

Learn to select multiple non-contiguous cells in Excel by holding Ctrl and clicking, and use Shift for contiguous ranges; see selections appear in color and in the address bar.

Apply Excel filters to view data by smallest to largest or largest to smallest, and filter by color or month to explore trends.

Explore practical Excel table formatting to elevate data visualization, including borders, fonts, fills, alignment, header styling, and color, to clearly present data to others.

Use the format printer to copy the look of a table to other tables in the document, including header borders and colors. Clear formatting to reset appearance or clear content.

Learn how paste special in Excel lets you copy only values (not formulas or formatting), handle lookup ranges, and paste comments or validations to share clean data.

Discover how paste special in Excel lets you copy formulas or values, preserve formatting, and apply data validation across ranges for accurate, efficient spreadsheets.

Learn how paste special operations in Excel transform a data range by adding, subtracting, multiplying, or dividing a constant, using copy, select range, and paste special techniques.

Learn two advanced paste special features in Excel, including skip blanks and transpose, to replace values and convert vertical data into horizontal layouts for monthly sales tables.

Discover how to remove duplicates in Excel by matching all four columns: first name, last name, item, and patison, and delete duplicate entries to keep only unique records for analysis.

Remove blank rows in Excel by selecting the dataset, pressing Ctrl+G to open Go To Special, choosing blanks, and deleting them from the Home tab to enable analysis.

Discover how to use Excel's fill range and filtering to place item names in front of buyers, using go to special and an entry formula to automate alignment.

Learn how to use Flash Fill in Excel 2013 to split an email list into first and last names, automatically generating a clean names list for analytics.

Learn to format addresses in Excel by inserting line breaks with Alt+Enter, using the formula bar to split lines for multi-line addresses and enhance data presentation.

Learn to create line breaks in an Excel address by concatenating house number, locality, and city state code using & with CHAR(10), producing a multi-line entry in column D.

Learn how to use Autofill and create custom lists in Excel, import lists, and drag to fill names or items across cells to avoid repetitive typing.

Learn to clean datasets by converting numbers stored as text into numbers in Excel, using either the convert to numbers option or a paste-special multiply trick.

Learn to clean datasets in Excel by using find and replace to remove stray dashes between employee identifiers and numbers, selecting all data and replacing with blank.

Learn to reset table formatting in Excel using the Home clear options, selecting to clear only formatting while preserving data, content, comments, or hyperlinks as needed.

Master how to convert text to lowercase in Excel using the lower function, with practical examples converting names and addresses.

Learn to convert text to uppercase in Excel using the upper function, apply it in cells, and copy the formula to format names for certificates and aesthetics.

Convert names consistently using proper case in Excel, highlight the most-used text function, and learn when to apply lowercase or uppercase for forms.

Create a single full name by joining three text columns in Excel, inserting spaces between first, middle, and last names, using a concatenate-like function.

Learn how to clean text in excel by removing extra spaces, including duplicate spaces between words and leading or trailing spaces, using a practical formula.

Learn how to measure string length, apply character limits such as 128 or 140 for tweets, and adjust text to fit constraints using a simple length check in Excel.

Learn how the Excel exact function enforces case sensitivity to compare two strings, returning false when names or spaces differ, with practical examples using bar and pie charts.

Learn how to compare text in Excel using the exact function for case sensitivity and a simple equality check for case-insensitive matches, by comparing column one to column two.

Apply the replace function in Excel to transform names and ticket identifiers by substituting initial alphabets with a coded prefix, automating pattern generation.

Learn to use the substitute function in Excel to replace old text with new text in a string, enabling updates like software versions and holiday calendars.

Learn how to use text to column in Excel to split delimited data by comma into name, gender, subject one, subject two, institute name, and year of admission for analysis.

Explore text to column delimiting in Excel to handle mixed delimiters like commas and semicolons. Learn how proper data cleaning transforms messy datasets into clear, ready-for-analysis formats for accurate insights.

Master the text to column feature in Excel with fixed width to split the country code +91 from the 10-digit mobile number for SMS and WhatsApp campaigns.

Explore relative referencing in Excel by calculating each business manager's percent achievement as achievement divided by target, and use drag fill to copy the formula across rows.

Learn how absolute references fix target-based incentives in Excel, applying a constant value across rows to compute accurate incentives as targets or rates change.

Learn how mixed references in Excel use dollar signs to freeze a column while letting the row change, enabling a single formula to build a 21–30 by 1–10 table.

Learn to compute three-day differences by summing visitors across two websites on different sheets using a 3D reference in Excel.

Learn how to name cells in Excel to replace absolute references with named ranges, speeding up formulas for large worksheets and reducing errors.

Apply data validation with a dropdown list sourced from a product list to ensure consistent product names in the order book, and configure input messages and error alerts.

Apply the text function with data validation to enforce invoice numbers between four and six digits in length in worksheets, testing alphanumeric inputs and error messages.

Apply Excel data validation to restrict entries to whole numbers within a defined range. Set the rule to between 5 and 25 units to block decimals and out-of-range quantities.

Validate a date in the worksheet using data validation, setting a between rule from May 5, 2020 to May 25, 2020 to ensure only valid orders are entered.

Apply data validation in Excel to restrict delivery times. Use between to allow 12:00 p.m. to 4:00 p.m. and not between to block other hours; include 24-hour formats like 15:00.

Learn to work on multiple sheets together in Excel, compute total targets and achievements across quarters, and update names or add a new salesperson across all sheets with few clicks.

Learn to sum data across multiple worksheets in Excel using a single formula, selecting from the first to the last sheet, and verify total targets and annual achievement.

Explore the datedif function in Excel to calculate days, months, and years between two dates, with examples using May 1, 2018 and June 1, 2020.

Learn to use the date value function in Excel to convert text dates into usable date values for charts and data visualization, and adjust chart start dates accordingly.

Master the date and time functions in Excel, including today’s date, today’s date with time, and current time, and learn dynamic versus static values, formatting, and dashboard applications.

Explore how the weekday function in Excel maps dates to a number 1–7, with 1 for Sunday and 2 for Monday, and how the text function displays the day name.

Learn to calculate working days between dates using the network days function in Excel, excluding weekends and holidays, with practical start date examples and holiday lists.

Learn how to calculate a person’s age in excel using the date function and DATEDIF, with three approaches for today’s date, a end date, and a specific date.

Compute days until your next birthday in Excel by building the next birthday date from year, month, and day functions, then calculating the days between today and that date.

Discover how to determine the last day of the month in Excel by using the end-of-month function, converting to a date value, and applying formatting to display the result.

Learn to calculate the time difference between two times in Excel by subtracting start from end, including same-day calculations and using now or today for current time.

Learn to use the round function in Excel to round to whole numbers, including round up and round down, and calculate wages by multiplying hours by rate.

Apply the round up function in Excel to convert working hours to the next whole number, then calculate pay by hours times rate.

Learn to apply the round down formula to convert hours to full hours, compute wages as working hours times pay, and enforce discipline and cash flow control.