Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstalliTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Excel has four built-in tools that can remove a surprising amount of repetitive work: Flash Fill infers a text transformation from an example, Quick Analysis previews totals and visuals, Power Query turns imports and cleanup into repeatable steps, and Power Query profiling reveals the shape of data before you analyze it.
They solve different problems, so the best choice depends on your starting point. Use Flash Fill for a one-off, recognizable text pattern; Quick Analysis for an immediate summary of a selected range; Power Query for a refreshable import-and-clean workflow; and profiling when you need to check quality, distributions, or row counts. Names and locations can vary by Excel edition and platform, so a missing command is often a version difference rather than a mistake.
Which Excel tool should you use?
The four features are complementary rather than competing. This table shows the decision in one view.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →| Tool | Input | Main outcome | Best use | What to remember |
|---|---|---|---|---|
| Flash Fill | Existing text plus one example result | A filled column based on the inferred pattern | Splitting, combining, or reformatting consistent text | Inspect results when source values are irregular |
| Quick Analysis | A selected range | Previewed totals, formatting, sparklines, or charts | Fast exploration without building a full report | The documented instructions are labeled for Excel 2016 |
| Power Query | An external connection or imported table | A loaded result built from saved shaping steps | Repeatable cleanup and refreshable imports | Commands and capabilities vary by platform and edition |
| Power Query profiling | Columns in a query | Statistics, distributions, quality indicators, and row counts | Checking data before modeling or reporting | The default profile covers the first 1,000 rows |
1. Flash Fill: split or combine text from an example
Flash Fill is the quickest option when you can demonstrate the transformation you want. Microsoft describes it this way: “Flash Fill automatically fills your data when it senses a pattern.” It can separate first and last names from a combined column, or combine names that are stored in separate columns. See the official examples in Microsoft’s Flash Fill guide.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
How to split names automatically
- Put the combined names in one column, with a header such as Full Name.
- In the adjacent column, type the desired first name for the first row. For example, if the source cell contains “Ada Lovelace,” type “Ada.”
- Move to the next cell in that new column. Excel may display a preview of the remaining first names.
- Press Enter to accept the preview. If no preview appears, select Data > Flash Fill or press Ctrl+E.
To extract last names, repeat the process in another column with the first desired surname as your example. The same approach works in reverse when you want to combine two columns: type the first complete name in a destination column, then accept the suggested pattern.
When Flash Fill is a good fit
- The source values follow a recognizable, consistent pattern.
- You need a quick transformation for a worksheet rather than a reusable import pipeline.
- The result is easy to spot-check against the source.
When to slow down and check the output
Flash Fill is pattern inference, not a rules engine. Mixed formats, missing names, suffixes, multiple spaces, or exceptions can produce results that look plausible but are wrong. Scroll through the completed column and compare unusual rows with the source before using the values in a report, export, or mail merge. If the examples are not consistent enough for a human to describe with one clear pattern, use a more controlled cleanup method instead.
2. Quick Analysis: preview totals and visuals without building them manually
Quick Analysis is designed for a selected block of data. Microsoft’s documented workflow offers previews for totals such as sum, average, and count, along with conditional formatting, sparklines, and charts. The cited instructions are labeled for Excel 2016, so the button, tabs, or available choices may not look identical in a current desktop, web, or Mac build. The reference is Microsoft’s Basic tasks in Excel page.
Rank #2
Preview a total
- Select a tidy range that includes the numbers you want to inspect. Keep headers clear and avoid selecting unrelated blank areas.
- Click the Quick Analysis button that appears near the lower-right edge of the selection.
- Open the Totals category.
- Hover over options such as Sum, Average, or Count to preview the result.
- Click the option you want to apply it to the worksheet.
The preview matters: it lets you test an idea without committing to a formula, PivotTable, or chart first. For a small sales range, for example, you can see whether a sum or average communicates more clearly before adding a permanent summary.
Preview formatting, sparklines, and charts
With the same range selected, use the other Quick Analysis categories to try conditional formatting, sparklines, or charts. A conditional-formatting preview can expose unusually high or low values; a sparkline can show the direction of a row of numbers in a compact cell; and a chart preview helps you judge whether the selected layout makes sense. Click only after the preview matches the question you are asking.
Limits of the shortcut
Quick Analysis is a rapid exploration aid, not a replacement for a carefully designed report. It acts on the current selection, so a poorly shaped range can produce an unhelpful chart or summary. If the Quick Analysis button or a category is absent, check the Excel version and platform: the Microsoft page documents a particular generation of Excel rather than promising identical controls everywhere.
3. Power Query: make importing and cleanup repeatable
Power Query, called Get & Transform in Excel, is the right tool when data arrives repeatedly or requires several cleanup operations. Microsoft defines the workflow as connecting to or importing external data and then shaping it, such as removing a column, changing a data type, or merging tables. The result can be loaded into Excel and refreshed later. Read the overview at About Power Query in Excel.
Free tools Windows power users keep installed
One-click scans. No signup required.
A practical import-and-clean workflow
- Open the Data tab and choose the available Get Data, From Table/Range, or equivalent Get & Transform command for your source.
- Choose the file, workbook, table, or connection and open it in the Power Query editor when prompted.
- Apply shaping steps in the order you need them. Typical documented operations include removing columns, changing data types, and merging tables.
- Review the preview after each step so you can see how the transformation changes the rows and columns.
- Use Close & Load (or the equivalent load command) to return the shaped result to Excel.
- When the source changes, use the query’s Refresh command instead of repeating the cleanup manually.
Each transformation is retained as part of the query workflow. That is the key difference from editing a worksheet by hand: the cleanup can be replayed when new data follows the same source structure.
Why this is safer than ad-hoc editing
- The original source remains separate from the transformed output.
- The sequence of cleanup operations is visible and repeatable.
- Changing a data type early can prevent later calculations from treating numbers or dates as text.
- Merging tables lets you combine related data without copying and pasting columns every time.
Power Query is listed by Microsoft for Excel on Windows, Mac, and the web, but detailed capabilities are not identical across every platform, license, or edition. Microsoft’s learning overview at Learn to use Power Query and Power Pivot in Excel and the feature comparison at BI capabilities in Excel and Office 365 are useful when a command is missing. Check the exact build you use before designing a workflow that depends on a particular connector or editor option.
Rank #4
What to do when Get Data is missing
First confirm that you are looking at the Data tab and that the workbook is open in an Excel edition that supports the needed Power Query capability. If the command still does not appear, compare your platform with Microsoft’s current feature documentation rather than assuming the query itself is broken. A workflow created on one platform may expose fewer controls on another.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.4. Power Query profiling: inspect data before you trust it
Profiling is a diagnostic layer inside Power Query. It shows statistics and distributions for query columns, helping you see whether imported data is complete and shaped as expected before you build formulas, charts, or models. The Microsoft profiling guide also documents a Count Rows command for checking the number of records.
Use profiling as an intake check
- Open the query in the Power Query editor.
- Turn on the available column-quality, column-distribution, or column-profile views from the editor’s profiling controls.
- Look for unexpected nulls, inconsistent values, unusual distributions, or columns whose type does not match their contents.
- Run Count Rows when you need the number of records in the current query result.
- Fix obvious issues with query steps, then profile again before loading the data.
The 1,000-row default matters
Microsoft documents that the default profile is based on the first 1,000 rows. That is a sampling default, not a maximum dataset size. If late-arriving rows can contain errors or rare categories, change the profiling setting to inspect the entire dataset before drawing conclusions. The option to profile the complete data is especially important when the source is sorted, because the first 1,000 rows may not represent the rest.
Best Value
A repeatable decision process
When a worksheet arrives, identify the job before choosing a feature:
- Is the job a single, obvious text transformation? Enter one example and try Flash Fill.
- Do you need a quick answer from a clean selection? Use Quick Analysis to preview a total or visual treatment.
- Will the same source arrive again? Build a Power Query that records the import and cleanup steps.
- Do you need to know whether the imported data is trustworthy? Profile the query and check the row count before analysis.
You can combine them. For example, profile an imported table in Power Query, load it, use Flash Fill for a small presentation-only text adjustment, and then use Quick Analysis to explore a selected result. Keep the repeatable source cleanup in Power Query; reserve one-off worksheet conveniences for tasks that do not need to be replayed.
Availability, version, and platform cautions
These features do not have one universal interface. Flash Fill instructions cover multiple Excel generations, Quick Analysis guidance on the cited Microsoft page is explicitly labeled for Excel 2016, and Power Query is described across Windows, Mac, and the web with edition-dependent differences. A missing button can therefore reflect the build, account, or platform you are using. Verify the labels in your own ribbon and consult Microsoft’s feature documentation before promising a workflow to a team that uses different Excel editions.
Bottom line
Flash Fill removes repetitive patterned typing, Quick Analysis gives you a fast preview of summaries and visuals, Power Query turns recurring imports into refreshable procedures, and profiling tells you what those imported rows actually contain. Start with the smallest tool that matches the job, inspect inferred or sampled results, and move recurring work into a query so the next update takes a refresh instead of another round of manual cleanup.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
