26. Excel Tricks Every Analyst Should Know

26.8 Checking and Sharing Tricks

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Before emailing a report, Ravindra Bagale turns formulas into values, flips a table sideways for a slide, looks for typed numbers and makes the input drop-down grow by itself. Four last tricks.

Trick 29. Freeze formulas as values before sending is shown in the box below; tricks 30–32 follow it.

First look at the data (before → after):

Expected result — After Paste Values

Select the formula cells and press Ctrl+C.
Without moving, click Home › Paste › Paste Values (or Ctrl+Alt+V › V › OK).
The numbers stay; the formulas are gone. Do this on a copy of the file.

Trick 30. Turn rows into columns with Transpose

Before:

After:

Steps:

1. Select A1:B3 and press Ctrl+C.
2. Click an empty cell, e.g. D1.
3. Press Ctrl+Alt+V, tick Transpose, click OK.
4. For a live link instead, use =TRANSPOSE(A1:B3) in D1.

Trick 31. Find every typed number in a sheet

Before:

After:

Steps:

1. Select the report area.
2. Click Home › Find & Select › Go To Special.
3. Choose Constants, tick only Numbers, click OK.
4. Give the selected cells a fill colour so you can review them.

Trick 32. A drop-down that grows with its Table

Before:

After:

Steps:

1. Put the cities in a Table named tblCities.
2. Define the name CityList as =tblCities[City] in Formulas › Name Manager › New.
3. Select the input cells, Data › Data Validation › List, Source =CityList.
4. Full lesson: 2.9 Named Lists for Drop-Downs.

Practice task (try it before you open the answer):

1. Transpose three cities and their sales.
2. Select every typed number in a report area.
3. Make the City drop-down pick up a new city automatically.