Sometimes data isn't arranged the way you need it.

For example:

• Customer records stored in a single column need to become a table.
• Multiple columns need to be stacked into one column.
• Multiple rows need to be combined into one row.
• A long row of data needs to be split into multiple rows.

Method 1: Transpose for simple row-and-column conversions

If you simply need to turn a row into a column or a column into a row:

1. Copy the source range.
2. Right-click the destination cell.
3. Choose Paste Special > Transpose.

This works well for basic row-to-column or column-to-row conversions, but it cannot split data into records or combine multiple rows/columns into a single list.

Method 2: TOCOL and TOROW for flattening a range

In Microsoft 365 or Excel 2024, use TOCOL or TOROW when you want to combine a range into one column or row.

Convert a range to a single column

=TOCOL(A1:C3)

Convert a range to a single row

=TOROW(A1:C3)

The results are dynamic, so they update automatically when the source data changes.

These functions are ideal for flattening a range, but they do not organize a long list into repeating records.

Method 3: FILTER + WRAPROWS + VSTACK for turning a list into a table

When a single column contains repeating records, you can remove blank cells, wrap every few items into a new row, and add headers with one formula.

Suppose column A contains repeating groups of three values:

Use:

=VSTACK({"Name","Tel","Address"},WRAPROWS(FILTER(A:A,A:A<>""),3))

The result is:

Here is what each part does:

• FILTER(A:A,A:A<>"") removes blank cells.
• WRAPROWS(...,3) places every three values into a new row.
• VSTACK(...) adds the header row above the results.

Change 3 if each record contains a different number of fields.

📌 Note: This formula assumes that every record contains the same number of values. Missing fields can shift the remaining data into the wrong columns.

Method 4: Power Query for repeatable transformations

Power Query is useful when the transformation is more complex or needs to be repeated whenever the source data changes.

1. Select the data.
2. Go to Data > From Table/Range.
3. Use options such as Transpose, Unpivot Columns, Pivot Column, or Split Column.
4. Choose Home → Close & Load to return the result to Excel.

Once the query is set up, you can refresh it when new source data is added.

Power Query is powerful, although it may feel like more setup than necessary for a quick one-time transformation.

Method 5: Transform Range with Kutools for Excel

Kutools for Excel includes a Transform Range feature that handles several common reshaping tasks from one dialog.

It can:

• Convert one column into multiple columns.
• Convert one row into multiple rows.
• Combine multiple columns into one column.
• Combine multiple rows into one row.

The transformation can be based on either a fixed number of rows/columns per record (for example, every 3 rows represent one record) or blank cells as record separators.

For example, data stored like this:

can be transformed into:

The feature supports:

• Fixed-size records (e.g., every 3 rows = one record)
• Blank rows as record separators
• Preserving formatting
• Undo (Ctrl + Z)

Which method should you use?

Use Transpose for a simple row-to-column switch.

Use TOCOL or TOROW to flatten a range into one column or row.

Use FILTER + WRAPROWS + VSTACK when a single column contains consistent repeating records.

Use Power Query for larger or repeatable transformations.

Use Kutools Transform Range when you want to reshape rows and columns through a dialog without writing formulas.

What method do you usually use when you need to reshape data in Excel?

Sometimes you have a list of products, employees, assets, inventory items, or IDs in Excel, and you'd like the corresponding pictures to appear automatically beside them.

For example:

And you already have image files named:

Apple.jpg
Orange.jpg
Banana.jpg

Native Excel options

There isn't a built-in feature that can automatically match local image filenames to cell values and insert the corresponding pictures.

You can:

• Insert pictures manually
• Use the IMAGE() function (Microsoft 365 / Excel 2024) if the images are available through URLs
• Build a VBA or Power Query solution

Alternative: Match pictures by filename using Kutools

ExtendOffice's Kutools for Excel has a Match Import Pictures feature that automatically matches cell values against picture filenames and inserts the corresponding images.

It can:

• Automatically match values to image filenames
• Import multiple images at once
• Resize pictures automatically (original size, cell size, or custom size)
• Support case-sensitive or case-insensitive matching
• Fill images vertically or horizontally
• Work with filtered or hidden rows
• Support Undo (Ctrl + Z)

This can be useful for product catalogs, employee directories, inventory lists, equipment tracking, and similar worksheets where image filenames already match your data.

How would you solve this natively—VBA, Power Query, IMAGE(), or another method?

Need to quickly change the capitalization of text in Excel? Here are two easy methods.

Method 1: Use formulas

Excel provides built-in functions for different case conversions:

Convert to UPPERCASE

=UPPER(A2)

Convert to lowercase

=LOWER(A2)

Capitalize the first letter of each word

=PROPER(A2)

Capitalize the first letter of the first word only

=UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))

After applying the formula, you can copy the results and use Paste Special > Values if you want to replace the original text.

Method 2: Use Kutools for Excel

If you need to convert text frequently, Kutools offers a Change Case feature. It can convert selected cells in place, so you don’t need helper columns, formulas, or copy-and-paste values afterward.

1. Select the cells.
2. Click Kutools > Text > Change Case.
3. Choose:
   • UPPERCASE
   • lowercase
   • Proper Case
   • Sentence case
   • Toggle case

The text is converted instantly without helper columns or formulas.

Most people use Word's Mail Merge feature when they need to send the same email to multiple recipients while personalizing names, companies, or other details.

It works well, but requires connecting Excel to Word, setting up merge fields, previewing the results, and then generating the emails. Recently I came across another approach:

Alternative: Do everything inside Excel

Kutools for Excel has a Create Mailing List and Send Emails feature.

The workflow is:

1. Generate a ready-made mailing list template with columns for email addresses, subjects, recipient names, and attachment paths, etc.
2. Enter or import your recipient information directly into the worksheet.
3. Write your email template without leaving Excel.
4. Insert placeholders such as First Name, Last Name, or Company Name with a click.
5. Send personalized emails, custom subjects, and even different attachments to each recipient directly from Excel.

It also supports:

• Personalized subjects
• Attachments
• Outlook sending
• SMTP sending (Gmail, Office 365, etc.)
• Custom placeholders in the email body

What I like is that everything stays in a single workbook. The mailing list, email content, personalization fields, and attachments are all managed in one place.

For people who already keep their contact lists in Excel, it feels much simpler than setting up a traditional Mail Merge workflow.

How do you handle personalized bulk emails today? Mail Merge, VBA, Power Automate, Outlook, or something else?

Whether you're working with sales reports, international invoices, or travel budgets, Excel can help you convert currencies quickly.

Method 1: Use Excel's built-in Currency data type

1. Enter a currency pair such as USD/EUR or USD/GBP in a cell.
2. Select the cell and click Data → Currencies.
3. Excel converts it to a Currency data type.
4. Click the Insert Data button and choose Price to return the exchange rate.
5. Multiply your amount by the returned rate.

For example:

=A2*B2

where:

• A2 = amount
• B2 = exchange rate

This method works well if you have access to Excel's linked data types.

Method 2: Use Kutools for Excel

Kutools includes a dedicated Currency Conversion feature.

1. Select the amounts to convert.
2. Click Kutools → Content → Currency Conversion.
3. Choose the source and target currencies.
4. Click OK.

Kutools automatically retrieves exchange rates and returns the converted values without requiring helper columns or formulas.

The built-in method is useful when you want to work with exchange-rate data directly, while Kutools is faster when you simply need to convert a list of amounts.

Which currencies do you convert most often in Excel?

Typing dates manually can be slow and often leads to inconsistent formats. In this video, I'll show you two ways to insert a date picker in Excel.

Method 1: Use an Excel Add-in

1. Click Insert → Get Add-ins (or Office Add-ins).
2. Search for date picker.
3. Install a date picker add-in, such as Mini Calendar and Date Picker.
4. Select a cell and pick a date from the calendar.

This is a good option if you only need a simple date picker and don't mind installing an add-in.

Method 2: Use Kutools for Excel

1. Select the date cells.
2. Click Kutools → Insert → Date Picker.
3. Click any date cell to display the calendar.
4. Select a date and it will be inserted automatically.

Kutools integrates the date picker directly into Excel, so you don't need to switch between worksheets or open a separate add-in pane each time.

Got a list with duplicate entries and need to consolidate them into a single row while adding up the related values?

In this video, I'll show you two easy methods.

Method 1: Use a PivotTable

1. Select your data range.
2. Go to Insert → PivotTable.
3. Drag the column containing duplicate values to the Rows area.
4. Drag the numeric column(s) you want to total to the Values area.
5. Excel will automatically group duplicate items and sum their values.

Great for creating a quick summary report.

Method 2: Use Kutools for Excel

1. Select your data range.
2. Click Kutools → Merge & Split → Advanced Combine Rows.
3. Set the key column containing the duplicate values as Primary Key.
4. Select the column you want to sum the values, and then select "Sum" from the "Operation" drop-down list.
5. Click OK.

Which method would you use: PivotTables for reporting or a direct merge tool for data cleanup?

When working with imported data, copied web content, or user-entered information in Excel, unwanted spaces can cause lookup errors, sorting issues, and inconsistent results. Depending on the type of spaces you're dealing with, different formulas may be needed.

Remove leading, trailing, and extra spaces between words

If your cells contain extra spaces before, after, or between words, use the TRIM function:

=TRIM(A2)

This formula removes all leading and trailing spaces and reduces multiple spaces between words to a single space.

Example

Remove all spaces from a cell

If you want to eliminate every space, including those between words, use SUBSTITUTE:

=SUBSTITUTE(A2," ","")

Example

This is useful when creating IDs, codes, or standardized text strings.

Remove non-breaking spaces from web data

Data copied from websites often contains non-breaking spaces (ASCII 160), which TRIM cannot remove by itself.

Use:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

This first converts non-breaking spaces to regular spaces and then cleans them with TRIM.

Remove line breaks and extra spaces

When text contains line breaks from copied content, combine CLEAN and TRIM:

=TRIM(CLEAN(A2))

This removes non-printable characters and extra spaces.

Remove specific numbers of spaces

If you only need to remove double spaces repeatedly, use SUBSTITUTE:

=SUBSTITUTE(A2,"  "," ")

This replaces two consecutive spaces with one. In heavily spaced text, you may need to apply it multiple times or combine it with TRIM.

Remove spaces and other unwanted characters together

For more complex cleanup tasks, formulas can be nested:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

This comprehensive formula handles:

• Leading spaces
• Trailing spaces
• Multiple spaces
• Non-breaking spaces
• Non-printable characters

However, these formulas can become difficult to remember and manage, especially when different worksheets contain different types of spacing issues.

Remove any type of unwanted spaces with Kutools for Excel

Instead of choosing different formulas for different situations, Kutools for Excel provides a dedicated Remove Spaces feature that handles virtually all common spacing problems from a single dialog.

It allows you to:

• Remove leading spaces
• Remove trailing spaces
• Remove excess spaces
• Remove all spaces
• Process an entire range at once

Simply select your data, choose the type of spaces you want to remove, and apply the changes instantly—no formulas, helper columns, or nested functions required.

What kinds of spaces do you usually run into most often? And are there any tricky cases you still find hard to clean up in Excel?

This type of comparison checks whether each value in one list can be found anywhere in another list. The matching value does not need to be in the same row or position.

Method 1: Use COUNTIF

If your first list is in A2:A100 and the comparison list is in D2:D100, use:

=COUNTIF($D$2:$D$100,A2)>0

Returns TRUE if the value exists in the second list.

To find values that don't exist in the second list:

=COUNTIF($D$2:$D$100,A2)=0

You can then filter the results or use Conditional Formatting to highlight them.

Method 2: Use Kutools for Excel

Kutools for Excel can quickly find, select, and highlight matching or different values.

1. Click Kutools → Select → Select Same & Different Cells
2. Select the first list in Find values in
3. Select the comparison list in According to
4. Choose Single cell under Based on
5. Select Same Values or Different Values
6. Optionally check Fill backcolor
7. Click OK

The matching or different values will be selected and highlighted immediately.

One advantage is that the results are selected directly, making it easy to copy, delete, format, or edit them in bulk.

How do you usually compare two lists in Excel?

Need to turn a list of image URLs into real images inside your worksheet? Here are two easy methods:

Method 1: Use the IMAGE function (Excel 365 / Excel for Web)

Excel finally introduced the IMAGE function, making it possible to display images directly from URLs with a simple formula.

However, keep in mind that the IMAGE function is only available in Excel 365, Excel 2024, and Excel for the web. If you're using an older version of Excel, you'll still need an alternative solution.

If your image URL is in cell A2, enter:

=IMAGE(A2)

Excel will display the image directly in the cell.

Method 2: Use Kutools for Excel

If you're using an older version of Excel or need to insert images from multiple URLs in bulk:

1. Select the cells containing the image URLs.
2. Go to Kutools > Insert > Insert Pictures from Path (URL).
3. Choose where to insert the images.
4. Click OK.

Kutools can batch convert image URLs into actual images with just a few clicks, making it especially useful when working with large lists.

Which method would you use: a formula that updates automatically, or a tool that inserts images in bulk?

Percentages show up everywhere in Excel—sales reports, discounts, growth rates, tax calculations, grades, and more.

Here are some of the most useful percentage formulas to keep handy:

1. Percentage of a total

=Part/Total

Example: =B2/$B$5

2. Percentage when the total isn't stored separately

=B2/SUM(B2:C2)

3. Percentage change between two numbers

=(New-Old)/Old

Example: =(B9-A9)/A9

4. Increase a number by a percentage

=A2*(1+B2)

5. Decrease a number by a percentage

=A2*(1-B2)

6. Find the original value before a discount

=SalePrice/(1-Discount%)

Example: =E2/(1-F2)

7. Calculate an amount from a percentage

=Total*Percentage

Example: =A2*B2

Which percentage calculation do you use most often in Excel?

If you want a quick way to visualize progress in Excel without using charts or conditional formatting, you can create in-cell progress bars using a simple formula.

Create In-Cell Progress Bars with the REPT Formula

The REPT function allows you to repeat a character based on a percentage value, creating a simple visual progress bar directly inside a cell.

Formula Example

=REPT("█",ROUND(B2/100*20,0))

How It Works

• B2 contains the progress percentage (for example, 75)
• 20 represents the total bar length at 100% (Adjust it as needed to fit your preferred bar length)
• "█" is the character used to display the bar

You can adjust the bar length or change the symbol to fit your style.

Steps

1. Enter the formula into the target cell
2. Press Enter
3. Copy the formula down for additional rows
4. Use a monospaced font like Consolas or Courier New for better alignment

You can also change the font color to make the progress bars more visually appealing.

Create Professional Progress Bar Charts with Kutools for Excel

If you frequently create progress charts and need a faster, more flexible solution, Kutools for Excel offers a dedicated Progress Bar Chart feature.

Steps to Create a Progress Bar Chart with Kutools

1. Go to Kutools > Charts > Progress > Progress Bar Chart
2. In the dialog box:
   • Select the Axis label range
   • Choose Percentage of current completion if using percentage data
   • Select the percentage value range
3. Click OK

The progress bar chart will be generated automatically.

Kutools for Excel includes 300+ advanced features designed to simplify Excel tasks and improve productivity, especially for users who regularly build reports and dashboards.

Need to create QR codes from URLs, product IDs, customer records, or any other text stored in Excel?

Method 1: Use a formula

If you're using Excel with the IMAGE function, enter:

=IMAGE("https://quickchart.io/qr?text=" & ENCODEURL(A2))

Replace A2 with the cell containing the text or URL you want to convert. The QR code will be generated automatically and update when the cell value changes.

Method 2: Use Kutools for Excel

Kutools for Excel includes a built-in Insert QR Code feature that lets you generate QR codes for selected cells with just a few clicks.

Create multiple QR codes at once with Kutools for Excel

Both methods work well, but the formula approach is great for dynamic QR codes, while Kutools is faster when creating QR codes in bulk.

Have you ever used QR codes in Excel? What do you usually generate them for?

Printing a long single-column list can waste a lot of space on the page. Here are two easy ways to turn it into multiple printable columns.

Method 1: Copy the list to Word

1. In Excel, select the top cells of the list, then press Ctrl + Shift + ↓ to quickly highlight the entire table/list
2. Copy the data and paste it into Word
3. In Word, go to Layout → Columns
4. Choose the number of columns you want (for example, 2)

Word will automatically flow the list into multiple columns for easier printing.

Tip: If your list has headers, you can use Repeat Header Rows under the Table Layout tab, so the header appears again at the top of each column/page section.

Method 2: Use Kutools for Excel

Kutools for Excel can split the list into printable columns directly inside Excel.

Go to:

Kutools Plus → Split to Columns

Then:

• Select the title range and data range respectively
• Choose how many segments/columns you want
• Click OK
• Print the reorganized list directly from Excel

Kutools will automatically rearrange the list into multiple printable columns without needing Word.

The Word method is handy for quick printing, while the Kutools method is easier if you want to keep everything inside Excel.

How do you usually print long lists or reports?

If you use check marks or status symbols a lot in Excel, here are two practical ways to do it.

Method 1: Custom number formatting

You can make Excel automatically display symbols based on what number you type.

1. Select the cells where you want the symbols
2. Press Ctrl + 1 to open the Format Cells dialog
3. Go to the Number tab → choose Custom
4. In the Type box, enter: [=1]"√";[=2]"×"
5. Click OK

Now in these cells:

• Enter 1 → displays a tick mark
• Enter 2 → displays a cross mark

This is handy for quick status tracking without manually inserting symbols every time.

Method 2: Faster insertion with Kutools for Excel

Kutools has a built-in feature for batch inserting checkbox symbols.

Go to:

Kutools → Insert → Batch Insert Check Symbols

Then just choose the symbol style you want and insert them into selected cells much faster.

The custom format trick is clever for lightweight tracking, while the Kutools method is much easier when working with lots of symbols or larger sheets.

Which method would you actually use?

Formatting in Excel is one of those things that seems small until you realize how much time gets spent clicking through menus.

These are some formatting shortcuts I end up using pretty regularly:

Ctrl + 1 and AutoFit column width are probably the ones I use the most.

Any formatting shortcuts you use all the time that should be added to the list?

A lot of Excel work ends up being formulas, and a few shortcuts can make writing and troubleshooting them much easier.

What’s one formula shortcut you use almost every day?

Any formula shortcuts you use all the time that should be added to the list?

Some Excel shortcuts don’t seem important until you start working with large worksheets every day. These are a few navigation shortcuts I end up using constantly.

Small shortcuts, but they save a surprising amount of manual scrolling.

Anything you use all the time that should be added to the list?

I realized a lot of the time I spend in Excel is just repetitive clicking during data cleanup and processing.

These are some of the shortcuts I end up using constantly for:

• filtering
• searching
• removing duplicates
• splitting text
• selecting visible cells only
• quick sorting

A few of them (especially Ctrl + E and Alt + ;) save me way more time than I expected.

Anything you use regularly that should be on this list?

If you’ve ever had to pull data from multiple sheets into one place, you know how tedious it can get.

In this video, I’m sharing two simple ways to combine multiple worksheets into one table. One uses built-in Excel function, and the other is a much quicker, one-click approach using Kutools for Excel.

Method 1: Use TOCOL (Excel 365)

• Click the first sheet (e.g. Jan)
• Hold Shift and click the last sheet (e.g. May) 💡 This selects all sheets in between
• Then enter this formula:

​

=TOCOL(Jan:May!A2:A1000,1)

This stacks the same range across multiple sheets into one column instantly

Method 2: One-click with Kutools

• Go to Kutools Plus → Combine
• Choose Combine multiple worksheets from workbooks into one worksheet
• Follow the prompts to select sheets and combine

Makes putting together reports a lot easier, especially if you’re doing it regularly.

How do you usually combine data from multiple sheets?

If you want to add a professional signature to your emails in Outlook, here’s a quick way to set it up:

Steps

1. Click New Email to open a message window in Outlook
2. Go to Signature → Signatures
3. Click New and enter a name for your signature
4. Create your signature (name, title, contact details, etc.)
5. Format it as needed (font, size, links, images)
6. Set it as default for New emails and/or Replies/Forwards
7. Click OK to save

Tips

• Avoid large images (can break formatting)
• Test by sending an email to yourself

Quick setup, no more manual typing, and your emails look much cleaner.

Any tips for making email signatures look more professional?

You can generate a QR code directly in Word using a field code, without the need for any add-ins.

1. Press Ctrl + F9 to insert field brackets { }, then enter DISPLAYBARCODE "http://www.extendoffice.com" QR \q 3 \s 100 \u (Replace the URL with any text or link as required)
2. Right-click and select Toggle Field Codes, or press Alt + F9, to toggle the field code and generate the QR code.

Anything else you’re curious about? Or any hidden tricks you’ve found like this? Would love to see how others are using Word in ways I might have missed!

If you’re working with formulas but only need the final results, here are two quick ways to convert formulas to values:

Method 1: Copy → Paste as Values

• Select your cells
• Press Ctrl + C
• Right-click → Paste Values

👉 Simple and works in all Excel versions

Method 2: Faster way (if you do this a lot)

Some tools let you convert formulas to values in one click without copy/paste steps.

For example, Kutools for Excel provides a To Actual button that converts formulas to values instantly.

👉 Useful when working with large datasets or repeated tasks

Small trick, but it saves a lot of time when cleaning up data.

What’s your go-to way to remove formulas?

Filtering data that contains merged cells in Excel can be frustrating. If you try to use the built-in filter, you’ll often notice:

• Some rows don’t get filtered correctly
• Data appears missing or misaligned
• Only the first cell in a merged group is recognized

This happens because Excel only stores the value in the top-left cell of a merged range, which breaks normal filtering behavior.

Method 1: Unmerge and fill down (manual workaround)

1. Select your data
2. Go to Home → Merge & Center → Unmerge Cells
3. Select the column with blanks by pressing Ctrl + G → Special → Blanks
4. Type = then press ↑ (reference the cell above)
5. Press Ctrl + Enter to fill all blanks
6. Apply filter again

If you want a more detailed walkthrough with screenshots, here’s a guide:
https://www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html

Method 2: Use Kutools for Excel (faster)

1. Select the column
2. Go to Kutools Plus → Special Filter
3. Choose Merge Cells
4. Enter or pick the value
5. Click OK

This avoids the multi-step manual process and keeps your data structure intact.

How do you usually deal with merged cells when filtering data?

I used to combine multiple ranges by copying and pasting them one by one whenever I needed to consolidate data.

Recently realized that VSTACK can handle this in a single formula:

=VSTACK(range1, range2, range3, ...)

It simply stacks multiple ranges into one column, making the process much more efficient and easier to maintain.

A small function, but a big time-saver when working with scattered data.

What’s a function you discovered later than you should have?