Explore, Learn, and Enrich Your Existence
Excel character count — A complete guide to counting characters. The basic way to count characters in Excel is with the LEN function. In practice, it can also be used to count specific characters/words, exclude spaces, remove line breaks, sum a range, and even calculate the number of bytes (LENB).
VLOOKUP maximum minimum — Complete solution for threshold matching and category-specific maximum and minimum. Solve VLOOKUP maximum minimum problems in 10 seconds. For maximum (≤) values below a threshold, use VLOOKUP (approximate), and for minimum (≥) values above a threshold, use XLOOKUP or INDEX+MATCH (-1). For maximum/minimum values and related values (product names …) by category,
Excel import web images — The easiest way to insert URL images into cells. To display images (URLs) from the web directly in an Excel cell, use the IMAGE function. For a large list, collect URLs with Power Query (From Web) and then …
Excel combine multiple columns into one column — Complete Guide to Horizontal Combinations and Vertical Stacks When you need to combine multiple columns into one column, the goal is twofold: (1) to combine values from the same row into one cell…
6 Ways to Join Two Columns with Spaces in Excel (Including the Latest Functions and Troubleshooting) The simplest way to join two columns with spaces in Excel is with the & or TEXTJOIN function. This article will help you solve it in 10 seconds…
Excel Concatenate with Space: The Complete Guide to Joining Text with Spaces in Excel If you're searching for the Excel concatenate with space problem, the quickest answer is =A2 & ” ” & B2. However, in practice, it's a blank cell…
SUMPRODUCT multi-condition AND/OR + partial match (wildcard) pattern collection. Complex aggregations can be completed in a single cell without auxiliary columns, simply by knowing the SUMPRODUCT multi-condition OR pattern. This article summarizes the AND/OR/NOT and eight partial match patterns so you can use them right away. Precedence: …
SUMPRODUCT vs. SUMIFS — When to Use Which? Choosing between SUMPRODUCT and SUMIFS has a significant impact on speed, readability, and maintainability. This article is organized with a 30-second checklist and three case studies to help you make a decision right away. First, let's look at the SUMPRODUCT guide and…
3 Ways to Remove Duplicates in Excel: Remove Duplicates, UNIQUE, and Power Query. Removing duplicate values in Excel is a breeze with three methods: the fastest Remove Duplicates function, the UNIQUE function that preserves original values, and Power Query, which handles large volumes and reuses reliably. Follow the steps below for practical use…
How to Reduce Excel File Size: A Complete Guide to Saving in XLSB (Binary) Format. The key to reducing Excel file size is changing the save format. The most effective way is to save in XLSB (binary). This article explains how to do this in just 10 seconds…
Mastering Excel Worksheet Shortcuts (Windows/Mac) When working in Excel, you spend more time searching for sheet tabs with your mouse than you might think. This article provides Excel worksheet shortcuts for both Windows and Mac, making it easy for even beginners to follow along. After reading, you'll be able to…
VLOOKUP vs. XLOOKUP: A Complete Guide to Formulas, Definitions, Usage, and Error Resolution. This comprehensive guide compares the key differences between VLOOKUP and XLOOKUP, covering exact, approximate, wildcard, multiple conditions, and error resolution. We recommend using XLOOKUP for the new design and progressive conversion for the legacy design. Quick Fix: =XLOOKUP(A2, …
Excel Data Listing, Part 1: Mastering Basic, Dynamic, and Dependent Drop-Down Lists. The key to Excel data listing is creating drop-down lists using data validation and managing the source as a table and dynamic array (UNIQUE/FILTER/SORT). This guide covers everything from basic lists to dynamic and dependent lists, with practical examples.
Select All Excel Images: 1-Minute Organizing with Ctrl+A, Selection Pane, and Go To Options. To select scattered images/shapes in one go, use Ctrl+A, the Selection Pane, and the Go To Special option (Go To Objects). After selecting, apply size, alignment, spacing, grouping, and locking controls all at once…
Remove Dotted Lines in Excel (Mac): Hide Page Breaks and Print Lines Cleanly. Most dotted lines that remain on sheets after print previewing in Excel on a MacBook can be instantly removed simply by switching views. If necessary, manually reset breaks, clear print areas, and more.
Master Excel Custom Formats: Keep Values the Same, but Display Them Clearly. Using cell formats and custom formats in Excel, you can keep values the same while changing only the display. Learn about the four-section structure (positive, negative, zero, text), number, date, and text codes, and practical reporting…
VLOOKUP: Concatenate Two Values: Mastering Helper Rows, CHOOSE, and XLOOKUP VLOOKUP concatenation is a technique that combines multiple criteria, such as product code and month, into a single combination key to achieve an exact match. The helper row is the most intuitive and fastest, and we'll also explore the CHOOSE trick and XLOOKUP/FILTER alternatives.
Excel RANK Group Ranking: Complete Overall and Partial Ranking Recipe with COUNTIFS If you need both overall ranking and group (partial) ranking in your report, calculate the overall ranking with RANK.EQ / RANK.AVG, and then count the number of comparisons within the group with COUNTIFS to create the ranking…
Displaying Excel Text and Formulas Together: Mastering TEXT, TEXTJOIN, and Dynamic Arrays When you need to display Excel text and formulas together in a report, the safest way is to first fix the number/date format with TEXT(), and then combine them with & or TEXTJOIN. …
Excel Filter Not Working Solution: 10 Ways to Fix AutoFilter and Advanced Filter Not Working The fastest way to fix Excel filter not working is to combine the data range into one block (remove blank rows/columns, unmerge), organize the headers and formats, and then…
Retrieving Multiple Values with VLOOKUP: FILTER·XLOOKUP·INDEX/SMALL Practical Patterns "Retrieving multiple values with VLOOKUP" isn't possible with just the basic function. However, using the FILTER/XLOOKUP/INDEX+SMALL pattern, you can either filter all results linked to a single key or extract only the top N. Quick…
Peak Period Clothing Sales Forecast by PLU & Daily Stock Normalization (Excel+Copilot+Solver) Create daily forecasts by PLU using 4-week history, inventory/stocking schedules, and peak sales plans. Calculate actual sales volumes reflecting inventory constraints, and then equalize the total number of pallets per day.
5 Excel Line Breaks — Alt+Enter, Wrap Text, CHAR(10), SUBSTITUTE, Ctrl+J Excel line breaks are a way to spread text within a cell across multiple lines. From the quickest manual line break, to automatic line breaks, formula-based line breaks (CHAR(10)), SUBSTITUTE/Replace, and Power…
The Complete Guide to Excel PDF Conversion and Reverse Conversion (5 Shots) There are many times when you need to export an Excel report to PDF or convert received PDF data back to Excel. This article covers everything from saving Excel to PDF to PDF to Excel…
We've compiled all the ways to use Excel "legally" for free, including practical, free Excel usage tips, including Excel for Web, Mobile, Education, Trials, and alternative tools. Follow along right away, from tables and formulas to sharing and printing. Quick Fix — Start now. 5 steps. Sign in to office.com (free…
Learn all Excel functions for free — A-Z function dictionary, examples, and practice sheets. This guide will help you master Excel functions for free, all in one place. It provides reproducible practice procedures, including dynamic arrays and the latest functions, without downloading. …
Create fast approximate lookups even with 100 million rows using XMATCH + INDEX (sorting and binary search). The XMATCH INDEX approximate lookup combination is fast and transparent, even for large volumes. By accurately determining the boundary index with XMATCH's binary search and extracting values with INDEX, the vulnerability of VLOOKUP is eliminated. Quick …
Approximate Mode VLOOKUP vs. Approximate Mode XLOOKUP — Boundary Value Pitfalls Explained When searching for values based on “ranges” such as grade tables, rate plans, and tax rates, using VLOOKUP Approximate Mode (TRUE) or XLOOKUP Approximate Mode often results in incorrect values at the boundary values (critical values). This…
When you need the maximum/minimum value with VLOOKUP: A complete guide to finding the exact answer. The VLOOKUP max/min problem can be divided into two parts: (1) finding the maximum/minimum "value itself" and (2) finding the label (name/code) of the row containing that value. Also…
