Pocket Guide to Excel Success

Discover how Excel stores dates and times as numbers, and how formats like currency and percentage affect display, while a leading apostrophe makes values strings.

Master Excel techniques to format alternate rows, making every other row blank or colored. Use simple formulas to number rows, filter by smallest to largest, and apply colors.

Discover three essential Excel data processing features: text to columns for CSV data, remove duplicates for a unique list, and find and replace to modify data across datasets.

Explore three essential Excel functions for data cleaning and text preprocessing: trim to remove leading and trailing spaces, len to count characters, and upper or lower to standardize text case.

Master Excel text extraction with left, right, mid, find, and search to pull ratings, dates, and keywords like laptop from reviews; learn case sensitivity and practical extraction techniques.

Explore the substitute and replace functions in Excel, showing how to replace stars with hyphens and modify text by position or by exact matches.

Explore Excel math functions to summarize historical sales data, using sum, max, min, sumifs, averageifs, countifs, and ifs for quarterly and yearly insights.

Explore excel date functions to create dates with year, month, and day; extract components; compute age in days with today; and determine week numbers and day of week.

Explore Excel’s lookup functions—index, match, vlookup, and hlookup—using a sales by member and region dataset to identify top performers, regional lows, and dynamic interplays via dropdowns.

Create drop-down lists for drivers, sales, member, product, and region by removing duplicates to form unique lists, then apply data validation to use the lists as drop-downs.

Explore how to refresh data with drop-downs in Excel using sumifs to filter multiple conditions, such as Ramon, Coke, and east, and see the analysis update on large dashboards.

Create dynamic charts that refresh with dropdown selections, powered by a sumifs-based data slice across sales member, product region, and quarter, with a dynamic chart header.

Group rows and columns with the data outline to create expandable sections, align and distribute charts with shape format, and strike out gridlines and headings to enhance the dashboard view.

Create a professional, navigable Excel dashboard in ten minutes by building a home page with tabs, links, headers, and polished visuals using icons, colors, and slicers.

Revamp your sheet in one minute by adjusting column widths, formatting numbers with commas, removing decimals, converting to percentages, styling headers and borders, and hiding gridlines.

Create a pivot table to analyze sales member performance by year and quarter, with units and attainment shown. Use filters to switch between products and regions for quick insights.

Create a dashboard-like pivot table by inserting a slicer for product and region, then arrange the slicers to the left to filter multiple regions and compare performance.

Create calculated fields in pivot tables to compute attainment percentages by dividing unit sales by target, ensuring correct results at any granularity.

Learn how to automate tasks in Excel with macros by recording a macro to copy from A4 to A6 and assign it to a shape for quick reuse.

Implement macros for GBP delivery by recording notes: date up to seven days prior, customer list, truck driver details, quantity, and a form with dynamic dropdowns saved by a macro.

Design an Excel form with header and logo, four data fields, last seven days dispatch data, drop-downs from customers and driver lists, quantity validation, and a save macro.

Learn how to save form data to the database tab with a macro, linking inputs, recording the macro, and addressing hardcoded cell references that overwrite the last row.

Learn to finalize an Excel macro by saving changes to the database, replacing hardcoded ranges with last used row approach, and turning off screen updating for a smoother user experience.

Explore a case study of macro automation using VBA to generate and print delivery notes, store data in normalized tables, and analyze performance via a dashboard.