10 Excel Tricks You NEED To Know For Any Job.

This is your clear and practical guide that takes you through 10 Excel tricks that you need to know for your job. Each trick is written out with a step-by-step guide so you can follow it easily in Excel (Windows or Mac).

ONE

Flash Fill — auto-fill patterns from an example

1. Put source data in a column (e.g., full names in A).

2. In the column next to it, type the desired result for the first row (e.g., first name only).

3. Press Ctrl+E (Windows) or Data → Flash Fill (or ⌘+E on Mac Excel where supported).

4. Excel fills remaining rows by detecting the pattern. Scan results and correct any mis-parsed rows.

***

TWO

Text to Columns — split a column into parts

1. Select the column to split (e.g., “Full Name”).

2. Go to Data → Text to Columns.

3. Choose Delimited (when data has commas, spaces, tabs) or Fixed width, then Next.

4. Choose the delimiter(s) (e.g., Space) and preview. Click Next → Finish.

5. Results: data will be split into adjacent columns; move/rename as needed.

***

THREE

Remove Duplicates — clean duplicate rows quickly

1. Select the range or entire table.

2. Data → Remove Duplicates.

3. Choose columns that define a duplicate (tick the relevant checkboxes).

4. Click OK; Excel reports how many duplicates were removed and how many unique remain. (Tip: make a copy first if you want to keep originals.)

***

FOUR

Conditional Formatting — highlight values that matter

1. Select the range you want to format.

2. Home → Conditional Formatting → choose a rule: Highlight Cells Rules, Top/Bottom Rules, or New Rule for custom formulas.

3. For example, pick Greater Than and enter a threshold, then pick formatting (fill, font).

4. Use Manage Rules to edit or add multiple rules; use formula rules like =A2>1000 for advanced conditions.

***

FIVE

PivotTable — summarize data in seconds

1. Click any cell inside your dataset.

2. Insert → PivotTable → choose New Worksheet or Existing Worksheet → OK.

3. Drag fields to Rows, Columns, Values and Filters in the PivotField pane.

4. For numeric fields, click the field dropdown → Value Field Settings to change aggregation (Sum, Count, Average).

5. Use Design and PivotTable Analyze tabs to format and filter — refresh with Refresh after data changes.

***

SIX

XLOOKUP / VLOOKUP — pull matching values from another table (Prefer XLOOKUP where available)

1. Place the cursor where you want the returned value.

2. XLOOKUP example: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

   • e.g. =XLOOKUP(E2, A:A, B:B, "Not found")

3. If XLOOKUP isn’t available, use VLOOKUP: =VLOOKUP(E2, A:B, 2, FALSE) (remember VLOOKUP needs the lookup column to be leftmost).

4. Press Enter and drag the formula down to fill.

***

SEVEN

Data Validation dropdowns — force clean inputs

1. Select cells where you want a controlled input.

2. Data → Data Validation. Under Allow, pick List.

3. In Source, type comma-separated items or select a range that contains allowed values.

4. Optionally set Input Message or Error Alert to guide users. Click OK.

5. Users now choose from a dropdown, preventing typos.

***

EIGHT

Paste Special (Values) — paste results without formulas

1. Copy the source cells (Ctrl+C).

2. Select the destination and right-click → Paste Special → choose Values (or press Ctrl+Alt+V, then V).

3. Click OK. This converts formulas into their static results (useful before sending files or reducing calculation load).

***

NINE

Freeze Panes (and Split) — keep headers in view

1. Click the cell below the rows and to the right of the columns you want to freeze (e.g., A2 to freeze row1).

2. View → Freeze Panes → Freeze Panes.

3. Use Freeze Top Row or Freeze First Column for quick options.

4. To remove: View → Freeze Panes → Unfreeze Panes.

5. Use Split if you need independent scroll areas.

***

TEN

Autofill & keyboard shortcuts — speed up repetitive work

1. To AutoFill formulas or sequences: put the initial value(s) in cells, hover the fill handle (bottom-right) and drag down.

2. Double-click the fill handle to auto-fill down to the last contiguous row.

3. Useful shortcuts:

   • Ctrl+D: fill down from the cell above

   • Ctrl+R: fill right

   • Ctrl+Z: undo

   • Ctrl+Arrow: jump to data edges

   • Ctrl+Shift+L: toggle filters

4. Learn a few shortcuts relevant to your workflow and you’ll save large amounts of time.

For more essential tips and tricks on using Microsoft 365 applications and Social Media platforms, be sure to check out That Office Guy’s YouTube Channel.