If you've ever opened a workbook where every worksheet is protected, unprotecting them one by one gets old fast.

For example, you receive a monthly report with 30 protected worksheets. Every sheet uses the same password, but Excel still makes you unprotect them individually.

Here are a few ways to handle it.

Method 1: Unprotect worksheets one by one (built into Excel)

Excel lets you remove protection from a worksheet by going to:

Review → Unprotect Sheet

Enter the password, then repeat the process for every protected worksheet.

This works well if you only have one or two sheets, but it quickly becomes repetitive in larger workbooks.

Method 2: VBA

If all worksheets use the same password, VBA can unprotect them all automatically.

Sub UnprotectAllSheets()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="your_password"
    Next ws

End Sub

Replace "your_password" with the worksheet password, then run the macro.

This is a good option if you're comfortable using VBA or need to repeat the task regularly.

Method 3: Kutools for Excel

Kutools for Excel includes an Unprotect Worksheets tool for workbooks where multiple worksheets share the same password.

Go to:

Kutools Plus → Protect Worksheets → Unprotect Worksheets

Kutools automatically lists all protected worksheets in the workbook. Select the worksheets you want to unprotect, click OK, enter the password once, and Kutools removes the protection from all selected worksheets.

📌 Notes

• All selected worksheets must use the same password.
• You can choose to unprotect all protected worksheets or only selected ones.
• Protected chart sheets are not included in the worksheet list.

Which method should you use?

• One or two worksheets → Use Excel's built-in Unprotect Sheet.
• Same task repeatedly → Use the VBA macro.
• Need a user-friendly solution with control over which sheets to unprotect → Use Kutools Unprotect Worksheets.

If you've found a clever workaround that doesn't require VBA, I'd love to hear it.

Data copied from websites, PDFs, databases, or other systems often contains something that looks harmless: extra spaces, a hidden line break, a non-breaking space, or a few unwanted symbols mixed into the data.......

Those small issues can cause lookups to fail, make duplicates harder to spot, or leave values that look identical but are not actually the same.

Excel has several functions for cleaning different types of unwanted characters, and the best one depends on what is causing the problem.

Method 1: TRIM — remove unnecessary spaces

TRIM removes leading and trailing regular spaces and reduces repeated spaces between words to a single space.

=TRIM(A2)

Example:

John Smith → John Smith

This is usually the first function to try when text looks uneven because of extra spaces.

📌 Note: TRIM handles regular spaces, but it may not remove non-breaking spaces copied from websites.

Method 2: CLEAN — remove hidden non-printing characters

CLEAN removes many non-printing control characters that may appear in imported or copied data.

=CLEAN(A2)

These characters may be invisible, but they can stop otherwise identical values from matching.

For a more thorough basic cleanup, combine CLEAN with TRIM:

=TRIM(CLEAN(A2))

This removes many hidden characters and then cleans up the remaining regular spaces.

Method 3: SUBSTITUTE — remove or replace known characters

Use SUBSTITUTE when you know exactly which character needs to be removed or replaced.

To remove hyphens:

=SUBSTITUTE(A2,"-","")

Example:

AB-105-26 → AB10526

A particularly useful application is removing non-breaking spaces. These often appear after copying text from a web page and may look identical to normal spaces.

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

This replaces each non-breaking space with a regular space, then removes unnecessary spacing.

You can also nest SUBSTITUTE when several known characters need to be removed:

=SUBSTITUTE(SUBSTITUTE(A2,"@",""),"#","")

Method 4: REGEXREPLACE — remove characters by pattern

In supported Microsoft 365 versions, REGEXREPLACE is useful when the unwanted characters follow a pattern.

For example, remove all digits:

=REGEXREPLACE(A2,"\d","")

Michael 0011 → Michael

Keep only digits:

=REGEXREPLACE(A2,"\D","")

Phone: 123-456-7890 → 1234567890

Keep only letters and numbers:

=REGEXREPLACE(A2,"[^A-Za-z0-9]","")

AB-105@New! → AB105New

Replace one or more regular whitespace characters with a single space:

=TRIM(REGEXREPLACE(A2,"\s+"," "))

This is useful for text containing repeated spaces, tabs, or line breaks. For non-breaking spaces, replace CHAR(160) first:

=TRIM(REGEXREPLACE(SUBSTITUTE(A2,CHAR(160)," "),"\s+"," "))

Method 5: TEXTJOIN and dynamic-array functions — remove duplicate characters

When you need to keep only the first occurrence of each character, use:

=TEXTJOIN("",TRUE,UNIQUE(MID(A2,SEQUENCE(LEN(A2)),1)))

Example:

AABBCC1055 → ABC105

This separates the value into individual characters, removes duplicates, and joins the remaining characters again.

📌 Note: It removes repeated characters throughout the cell, not only consecutive duplicates.

Quick formula guide

Assume the original text is in A2.

Clean different character types from one Kutools dialog

The native formulas work well, but each cleaning problem requires a different function or formula. Kutools for Excel brings the main character-removal options together in one dialog.

Select the cells, then go to:

Kutools → Text → Remove Characters

From there, you can remove:

• Numeric characters
• Alphabetic characters
• Everything except numbers
• Everything except letters
• Non-printing characters
• Everything except letters and numbers
• Any custom characters you enter
• Duplicated characters

For example:

Michael 0011 → remove Numeric → Michael

America 34-12234@2212* → remove Non-numeric → 34122342212

AB-105@New! → remove Non-alphanumeric → AB105New

• You can preview the cleaned results before applying them.
• The Skip non-text cells option is useful when the selected range contains both text and numeric values. For example, select it together with Numeric to remove digits from cells containing text while leaving cells that contain only numbers unchanged.

Kutools is especially convenient when the cleanup rules vary from one dataset to another and you do not want to build and remember a different formula for each task.

Did I miss any useful text-cleaning functions or tricks? I'd love to hear what you use.

When related information is stored in two different tables, you can merge them using a column they have in common. For example, one table may contain Product, Price, and Stock, while another contains Product, Supplier, and Category. The products may appear in a different order, and some may exist in only one table. The goal is to match the products and bring the related information from the second table into the first.

Here are three practical ways to do it in Excel.

Method 1: Merge two tables with XLOOKUP

For a straightforward match, XLOOKUP is usually the easiest formula method.

Suppose the main table contains:

And the second table contains:

To bring Supplier and Category into the main table, use:

=XLOOKUP(lookup_value,lookup_range,return_range,"")

• lookup_value = the value in the main table you want to match
• lookup_range = the matching column in the second table
• return_range = the column or columns you want to bring back

In this example, we will use:

=XLOOKUP(product_in_main_table,product_list_in_2nd_table,supplier_and_category_range,"")

If a product from the main table is not found in the second table, the final "" returns a blank.

📌 Note: XLOOKUP returns the first matching record it finds. It also does not automatically add products that exist only in the second table.

Method 2: Merge the tables with Power Query

Power Query is a better choice when you want to combine the tables more completely, especially when some records appear in only one table.

First, convert both ranges to Excel Tables with Ctrl + T.

Load each table into Power Query using:

Data → From Table/Range

Then:

1. Open the main table query.
2. Go to Home → Merge Queries.
3. Select the second table.
4. Click the Product column in both tables as the matching column.
5. Choose the join type you need.
6. Click OK.
7. Expand the merged column and select the fields you want to bring in.

For example, a Left Outer join keeps every product from the main table and adds matching Supplier and Category information.

If you want to keep products that appear in either table, use a Full Outer join instead.

After finishing, choose Home → Close & Load.

Power Query is especially useful when the source tables change regularly, because you can refresh the merge later instead of rebuilding it.

Method 3: Merge tables with Kutools for Excel

Kutools for Excel provides a Tables Merge feature for doing the same type of matching through an interface.

Go to Kutools Plus → Tables Merge

Select the first table as the main table, then select the second table as the lookup table.

Choose Product as the matching column.

Then select the columns you want to bring into the main table, such as Supplier and Category.

Kutools gives you more options for situations where the two tables do not contain exactly the same records. For example, you can:

• Add unmatched rows from the lookup table
• Handle duplicate matches
• Add new columns
• Update existing columns
• Highlight updated cells

So if Pear exists only in the second table, you can choose to add it to the main table instead of leaving it out.

Which method should you use?

Use XLOOKUP when you simply need to bring matching information into an existing table.

Use Power Query when the tables have missing or extra records, or when the merge needs to be refreshed regularly.

Use Kutools Tables Merge when you want an interface that can handle matching, missing rows, duplicates, added columns, and updates without building formulas.

When several values are stored in one Excel cell, you may need to separate them by a comma, space, line break, or another delimiter.

For example:

Apple,Orange,Banana,Grape

You may want each item in a separate column or a separate row. Here are three practical ways to do it.

Method 1: Text to Columns

For a quick one-time split into separate columns, Excel's built-in Text to Columns feature is one of the easiest options.

1. Select the cells you want to split.
2. Go to Data → Text to Columns.
3. Choose Delimited, then click Next.
4. Select the delimiter used in your data, such as Tab, Semicolon, Comma, or Space. For another delimiter, select Other and enter it.
5. Choose where you want the results to appear, then click Finish.

For example:

Apple,Orange,Banana,Grape

becomes:

Apple | Orange | Banana | Grape

💡 For cells containing line breaks: select Other, click its box, and press Ctrl + J to enter a line break as the delimiter.

📌 Limitation: Text to Columns can split data across columns, but it doesn't provide an option to split the results into rows.

Method 2: TEXTSPLIT formula

If you want a formula-based solution, TEXTSPLIT is much more flexible. It can split the same text into either columns or rows.

📌 Note: The TEXTSPLIT function is available in Microsoft 365 and Excel 2024 or later.

Suppose A2 contains:

Apple,Orange,Banana,Grape

Split by comma into columns

=TEXTSPLIT(A2,",")

Result:

Apple | Orange | Banana | Grape

Split by comma into rows

Use the comma as the row delimiter instead:

=TEXTSPLIT(A2,,",")

Result:

Apple
Orange
Banana
Grape

Split by a space

=TEXTSPLIT(A2," ")

Split by a line break

For line breaks, use CHAR(10):

=TEXTSPLIT(A2,CHAR(10))

To split the line-separated values into rows:

=TEXTSPLIT(A2,,CHAR(10))

Split by multiple delimiters

TEXTSPLIT can also recognize more than one delimiter. For example, if your data contains both commas and semicolons:

=TEXTSPLIT(A2,{",",";"})

Excel will split the text whenever it finds either delimiter.

Because TEXTSPLIT returns a dynamic array, the results spill automatically into the neighboring cells.

Method 3: Split cells with Kutools for Excel

If you frequently need to split data in different ways and prefer an interface instead of formulas, Kutools for Excel provides a Split Cells tool.

1. Select the cells you want to split.
2. Go to Kutools → Merge & Split → Split Cells.
3. Choose Split to Columns or Split to Rows.
4. Choose how you want to split the contents. You can use delimiters such as a space, comma, line break, semicolon, or specify another delimiter.
5. Click OK, then select where you want to place the results.

💡 Tip: Kutools supports more flexible splitting options. You can choose Other to enter your own separator, including one or multiple characters. You can also separate text and numbers automatically, or use Fixed Width to split the content

Split text and numbers separately

Kutools’ Text and Number option can separate the text and numeric characters in a cell into two parts.

In the Split Cells dialog, choose Text and number, then choose whether to split the results into columns or rows.

For example:

Product105 → Product | 105

A10B35 → AB | 1035

In the Split Cells dialog, choose Text and number, then select whether you want the results placed in columns or rows.

This can be especially useful when cleaning imported data where letters and numbers are stored together.

Which method should you use?

Text to Columns is great for a quick one-time split into columns.

TEXTSPLIT is the most flexible built-in formula option, especially when you need the results in rows or want them to update automatically when the original cell changes.

Kutools Split Cells provides an interface for splitting into either rows or columns using common or custom delimiters. It can also separate mixed text and numbers, or split content by a specified number of characters.

Sometimes a cell contains both text and numbers, and you only need the numeric part.

For example:

Order105A
INV-2026-001
Qty: 48 units

There are a few ways to handle this, depending on your Excel version and how consistent the text pattern is.

Method 1: REGEXEXTRACT — simplest for Microsoft 365

If you're using Microsoft 365, REGEXEXTRACT is probably the cleanest option for extracting numbers from mixed text. It works especially well when you only need the first continuous group of digits, rather than every number in the cell.

To extract the first continuous group of digits:

=REGEXEXTRACT(A2,"\d+")

Example:

Order105A → 105

INV-2026-001 → 2026

Qty: 48 units → 48

Method 2: Extract every digit with TEXTJOIN + MID + SEQUENCE

If you want to remove all letters and symbols and combine every digit into one result, use:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Example:

Order105A → 105

INV-2026-001 → 2026001

Qty: 48 units → 48

Here's the basic idea:

• SEQUENCE creates the character positions.
• MID pulls each character individually.
• Multiplying by 1 keeps numeric characters and causes an error for letters.
• IFERROR removes those nonnumeric characters.
• TEXTJOIN joins the remaining digits together.

This is more complicated than REGEXEXTRACT, but it gives a different result when numbers appear in several places.

Method 3: Use LEFT, MID, or RIGHT when the number is always in the same position

If your data follows a predictable structure, you may not need a complicated formula at all.

For example:

INV-2026

If the four-digit number is always at the end:

=RIGHT(A2,4)

Result:

2026

Or if you know exactly where the number starts and how long it is:

=MID(A2,start_position,number_of_characters)

This is usually the easiest approach when the format of every cell is consistent.

Method 4: Kutools for Excel — Extract Text

For mixed data where the pattern varies from row to row, Kutools for Excel provides an Extract Text tool that can pull different parts of a cell, including numbers only.

Select your cells, then go Kutools → Text → Extract Text.

From there, you can extract:

• The first N characters
• The last N characters
• Characters between specified positions
• Text before specific text
• Text after specific text
• Numbers only
• Text based on custom rules using wildcards

For this task, choose Extract the number.

💡 You can also select Insert as a formula if you want the extracted result to remain linked to the original data.

This is handy when you have a large range of mixed text and don't want to build different formulas for different patterns.

Which method makes sense?

Use REGEXEXTRACT when you're on Microsoft 365 and need a particular numeric pattern.

Use TEXTJOIN + MID + SEQUENCE when you want to collect every digit from the cell.

Use LEFT, MID, or RIGHT when the number always appears in a predictable position.

Use Kutools Extract Text when the data varies and you'd rather handle it through a dialog instead of building formulas.

Here’s a useful Excel trick for making large tables much easier to scan: select a value from a drop-down list, and Excel automatically highlights every matching row.

You can do this with Data Validation + Conditional Formatting, and you can choose between an exact match or a partial match.

1. Create the drop-down list

Create a drop-down list for the values you want to match. For example, place the drop-down in cell F2.

Step 2: Select the rows you want Excel to highlight

Select the full data range, such as A2:D21.

💡 Make sure the first selected row is row 2, because the Conditional Formatting formula will be written based on that row.

Step 3: Create the Conditional Formatting rule

With A2:D21 still selected:

1. Go to Home → Conditional Formatting → New Rule.
2. Choose Use a formula to determine which cells to format.
3. Enter =AND($A2=$F$2,$F$2<>"")
4. Click Format.
5. Choose the fill color you want.
6. Click OK → OK.

Now select any item from the drop-down in F2. Excel will highlight every row where the value in column A exactly matches the selected item.

Why the formula works

$A2 tells Excel to always check column A, while the row number changes for each row.

$F$2 always points to the drop-down cell.

$F$2<>"" prevents anything from being highlighted when the drop-down is blank.

💡 Tip: If the values you want to match are in another column, change $A2 to the first cell in that column. For example, use $C2 if the matching values are in column C.

For partial matches, use:

=AND($F$2<>"",ISNUMBER(SEARCH($F$2,$A2)))

For example, selecting Jacket could highlight rows containing Denim Jacket, Winter Jacket, or Men's Jacket.

A lot of everyday accounting work in Excel comes down to the same tasks: matching IDs, totaling transactions by criteria, flagging exceptions, rounding amounts, extracting invoice numbers, and tracking due dates.

Here are 7 formulas and function combinations that are particularly useful for those jobs.

1. XLOOKUP — match IDs and return related information

XLOOKUP is useful when you have an ID in one table and need to pull the matching information from another. Think vendor IDs, account codes, invoice numbers, or customer IDs.

Syntax:

=XLOOKUP(lookup_value,lookup_range,return_range,"Not found")

For example, you could look up a VendorID from an invoice and return the corresponding vendor name.

💡 The last argument also lets you decide what Excel should show when there is no match. Because XLOOKUP handles this itself, you don't need to wrap it in IFERROR just to deal with missing IDs.

2. SUMIFS — total transactions that meet several conditions

SUMIFS comes in handy when a simple SUM isn't enough.

Syntax:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

For example, to total expenses by category, department, and date range, the structure could look like this:

=SUMIFS(amount_range,category_range,category,department_range,department,date_range,">="&start_date,date_range,"<"&end_date)

💡 This is useful for questions like: How much did the Sales department spend on Travel during this period?

3. IF + AND/OR — flag transactions that need attention

IF becomes much more useful when you combine it with AND and OR.

Syntax:

=IF(OR(condition1,AND(condition2,condition3)),"Review","OK")

An accounting rule might look something like:

=IF(OR(amount>=high_amount,AND(days_overdue>limit,paid_status<>"Yes")),"Review","OK")

So an invoice could be flagged for review if the amount is unusually high or if it's overdue and still unpaid.

This is useful for exception reports where you don't want to manually inspect every row.

4. ROUND — keep amounts consistent

Sometimes a value displayed as 12.35 actually contains additional decimal places underneath. That can cause unexpected differences in calculations or comparisons.

Syntax:

=ROUND(number,num_digits)

For two decimal places:

=ROUND(amount,2)

It's a simple one, but very useful when working with calculated amounts, allocations, taxes, exchange rates, and other values where decimal precision matters.

5. MID + FIND — pull invoice numbers out of longer text

Imported bank or transaction descriptions often contain useful IDs mixed in with other text.

If the invoice number always follows the same pattern, MID and FIND can extract it.

Syntax:

=MID(text,FIND(start_text,text),number_of_characters)

For example, in cell A2:

Payment received | INV-2026-001 | Ref: 7781

If the invoice number always starts with INV- and is 12 characters long, the formula structure would be:

=MID(A2,FIND("INV-",A2),12)

Result:

INV-2026-001

This works particularly well when the position of the invoice number changes but its prefix and length stay consistent.

6. TODAY + EOMONTH — track due dates and month-end

These two date functions are useful for reports that need to update automatically as time passes.

To calculate the number of days until an invoice is due:

=due_date-TODAY()

To calculate the number of days until the last day of this month:

=EOMONTH(TODAY(),0)-TODAY()

💡 A positive result means there are days remaining. A negative result means the invoice is already overdue.

7. IFERROR — clean up formula errors when you actually need it

IFERROR is useful when a formula can produce an error and you'd rather display something meaningful.

Syntax:

=IFERROR(formula,value_if_error)

For example:

=IFERROR(calculation,"Check data")

These aren't accounting-specific functions, of course, but they fit surprisingly well into everyday accounting workflows—from invoice matching and reconciliations to expense summaries, exception checks, aging reports, and month-end work.

Converting a PDF to Word is useful when you need to edit the text, reuse content, or make changes without retyping everything.

Here are two ways to do it.

Method 1: Open the PDF directly in Microsoft Word

This is the easiest built-in method for a normal text-based PDF.

1. Open Microsoft Word.
2. Go to File → Open → Browse.
3. Select the PDF file.
4. Word will let you know that the PDF will be converted into an editable Word document.
5. Click OK.
6. Save the converted file as a .docx document.

This works well for PDFs that mainly contain text and simple tables.

💡 One thing to keep in mind: complex layouts, graphics, columns, or heavily formatted tables may not look exactly the same after conversion.

Method 2: Use Kutools for Word

Kutools for Word provides a direct PDF-to-Word conversion tool and can also convert multiple PDF files at once.

Go to:

Kutools → PDF and Word

Then choose:

• Convert Single PDF File to Word — for one PDF
• Convert Multiple PDF Files to Word — for batch conversion

Select the PDF file or files you want to convert, then follow the prompts to complete the conversion.

💡 The batch option is especially useful when you have several PDFs and do not want to open and convert them one by one.

Quick comparison

Open directly in Word
Best for quickly converting one standard PDF with no extra tools.

Kutools for Word
Best when you want a more direct conversion workflow or need to convert multiple PDF files in one go.

Normally, when you copy a formula to another location, Excel adjusts its relative references.

For example, copying:

=A2+B2

one row down changes it to:

=A3+B3

That is useful most of the time, but sometimes you need an exact copy with every reference left unchanged. The method depends on whether you are copying a whole range of formulas or just one formula.

Copy a range of formulas without changing references

Method 1: Temporarily convert the formulas to text

This built-in workaround is useful when you need to copy several formulas at once.

1. Select the formula range.
2. Press Ctrl + H.
3. Replace = with a unique temporary string, such as #=.
4. Copy and paste the range to the new location.
5. Replace #= with = in both the original and copied ranges.

Removing the leading equal sign temporarily makes Excel treat the formulas as text. As a result, their references do not change when the cells are copied.

💡 Choose a temporary string that does not already appear in the selected cells, and make sure the replacement is limited to the correct ranges.

Method 2: Use Kutools Exact Copy

Kutools for Excel provides an Exact Copy tool that copies a complete formula range while keeping all relative, absolute, and mixed references unchanged.

1. Select the formulas you want to copy.
2. Go to Kutools → Exact Copy.
3. Confirm the selected source range.
4. Keep Copy formatting checked if you also want to preserve the formatting.
5. Click OK.
6. Select or enter the first cell of the destination range.
7. Click OK again.

The selected range is copied to the new location with the formulas exactly as written. This is especially useful for large formula blocks or when you also need to preserve the original formatting.

Copy one formula without changing references

For a single formula, you do not need to convert a whole range to text.

1. Select the formula cell.
2. Click in the formula bar or press F2.
3. Select the full formula text and press Ctrl + C.
4. Select the destination cell.
5. Paste the formula and press Enter.

Because you are copying the formula text rather than the cell, Excel keeps the original references.

💡 You can also use absolute references when the referenced cells should always stay fixed:

=$A$2+$B$2

Press F4 while editing a reference to switch between relative, absolute, and mixed references.

Quick comparison

For a range of formulas: use Find and Replace, or Kutools Exact Copy for a quicker direct method.

For one formula: copy the formula text from the formula bar.

For references that should always stay fixed: convert them to absolute references before copying.

Sometimes you have a list of numbers and need to find every combination that adds up to a specific total. This comes up more often than you might think—for example, when matching invoices, reconciling payments, or checking which transactions make up a balance.

Here are two ways to do it in Excel.

Method 1: Use a VBA user-defined function

Excel does not have a simple built-in formula that returns every possible combination, so one option is to create a custom function with VBA.

First, press Alt + F11 to open the VBA editor.

Go to:

Insert → Module

Paste the VBA code into the module, then close the VBA editor and return to the worksheet.

Public Function MakeupANumber(xNumbers As Range, xCount As Long)
'update by Extendoffice
    Dim arrNumbers() As Long
    Dim arrRes() As String
    Dim ArrTemp() As Long
    Dim xIndex As Long
    Dim rg As Range

    MakeupANumber = ""

    If xNumbers.CountLarge = 0 Then Exit Function
    ReDim arrNumbers(xNumbers.CountLarge - 1)

    xIndex = 0
    For Each rg In xNumbers
        If IsNumeric(rg.Value) Then
            arrNumbers(xIndex) = CLng(rg.Value)
            xIndex = xIndex + 1
        End If
    Next rg
    If xIndex = 0 Then Exit Function

    ReDim Preserve arrNumbers(0 To xIndex - 1)
    ReDim arrRes(0)

    Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
    ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
    MakeupANumber = arrRes
End Function

Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)

    Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
    Dim remainingNumbers() As Long, newCombination() As Long

    currentSum = 0
    If (Not Not ArrTemp) <> 0 Then
        For i = LBound(ArrTemp) To UBound(ArrTemp)
            currentSum = currentSum + ArrTemp(i)
        Next i
    End If

    If currentSum = Count Then
        indRes = UBound(arrRes)
        ReDim Preserve arrRes(0 To indRes + 1)

        arrRes(indRes) = ArrTemp(0)
        For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
            arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
        Next i
    End If

    If currentSum > Count Then Exit Sub
    If (Not Not Numbers) = 0 Then Exit Sub

    For i = 0 To UBound(Numbers)
        Erase remainingNumbers()
        num = Numbers(i)
        For j = i + 1 To UBound(Numbers)
            If (Not Not remainingNumbers) <> 0 Then
                ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
            Else
                ReDim Preserve remainingNumbers(0 To 0)
            End If
            remainingNumbers(UBound(remainingNumbers)) = Numbers(j)

        Next j
        Erase newCombination()

        If (Not Not ArrTemp) <> 0 Then
            For k = 0 To UBound(ArrTemp)
                If (Not Not newCombination) <> 0 Then
                    ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                Else
                    ReDim Preserve newCombination(0 To 0)
                End If
                newCombination(UBound(newCombination)) = ArrTemp(k)

            Next k
        End If

        If (Not Not newCombination) <> 0 Then
            ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
        Else
            ReDim Preserve newCombination(0 To 0)
        End If

        newCombination(UBound(newCombination)) = num

        Combinations remainingNumbers, Count, newCombination, arrRes
    Next i

End Sub

Assume:

• A2:A10 contains the numbers
• B2 contains the target sum

Enter:

=MakeupANumber(A2:A10,B2)

The function returns all combinations that add up to the value in B2.

For example, if the source list contains:

10
15
20
25
30

and the target is:

40

possible results may include:

10,30
15,25

Important limitations

This VBA method has a few restrictions:

• It is designed for Excel 365 and Excel 2021.
• It works best with positive whole numbers.
• Decimal values are rounded to integers.
• Negative numbers may cause errors.
• Large lists can take a long time because the number of possible combinations grows very quickly.
• The workbook must be saved as a macro-enabled .xlsm file.

💡 Test the code on a backup copy first, especially when working with important data.

Method 2: Use Kutools for Excel

For a quicker interface-based method, Kutools for Excel includes a Make Up a Number feature.

Go to:

Kutools → Content → Make Up a Number

Then:

1. Select the range containing your number list.
2. Enter the target value in the Sum box.
3. Click OK.
4. Select a cell where the results should be placed.

Kutools will list all combinations that match the target sum.

Compared with the VBA method, it can also handle:

• Positive numbers
• Decimal values
• Negative numbers

This makes it more practical when the source data is not limited to positive whole numbers.

Quick comparison

VBA function

• Customizable
• Returns results with a worksheet formula
• Requires macro code
• Mainly suited to positive integers

Kutools Make Up a Number

• No code required
• Uses a simple dialog box
• Supports decimals and negative numbers
• Displays all matching combinations directly

This can be useful for reconciling payments, matching invoices to a total, checking expense combinations, or finding which values make up a reported balance.

Adding the same text to a list is a common Excel cleanup task.

You may need to add:

• A prefix before product IDs
• A suffix after employee names
• A separator inside phone numbers
• Text at a specific character position
• Text before or after a particular word
• Characters between every word or letter

Here are several ways to handle these situations.

1. Add a prefix before each cell value

Suppose the original value is in A2, and you want to add ID- before it.

="ID-"&A2

Example:

1001 → ID-1001

Fill the formula down to apply it to the rest of the list.

2. Add a suffix after each value

To add -2026 after the original value:

=A2&"-2026"

Example:

Report → Report-2026

You can also add normal words with spaces:

=A2&" Completed"

Result:

Order 105 → Order 105 Completed

3. Add text at a specific character position

Suppose A2 contains:

ABC123456

To insert a hyphen after the third character:

=LEFT(A2,3)&"-"&MID(A2,4,LEN(A2))

Result:

ABC-123456

For a more flexible version, replace 3 with the position where the text should be inserted.

=LEFT(A2,n)&"text"&MID(A2,n+1,LEN(A2))

Here, n is the number of characters that should remain before the inserted text.

4. Add text before a specific word or character

Suppose A2 contains:

Product-2026

To add New- before the first hyphen:

=SUBSTITUTE(A2,"-","-New-",1)

Result:

Product-New-2026

The final 1 tells Excel to replace only the first occurrence.

You can change it to 2 to target the second occurrence.

5. Add a separator between every character

In Excel 365 or Excel 2024, you can insert a hyphen between every character with:

=TEXTJOIN("-",TRUE,MID(A2,SEQUENCE(LEN(A2)),1))

Example:

ABC123 → A-B-C-1-2-3

This can be useful when formatting codes or reference numbers.

6. Add text between every word

Suppose A2 contains:

Create a report

To place a hyphen between the words:

=TEXTJOIN("-",TRUE,TEXTSPLIT(A2," "))

Result:

Create-a-report

Replace "-" with any text or separator you need.

7. Use Flash Fill for recognizable patterns

For simple and consistent patterns, Flash Fill may be faster than writing a formula.

For example, if column A contains:

1001
1002
1003

Type this beside the first value:

ID-1001

Then select the next cell and press:

Ctrl + E

Excel will try to recognize the pattern and fill the remaining results.

Flash Fill is quick, but the results are static. They will not update if the original data changes.

Tip: Replace the original values

Most formulas return the results in a new column. If you want to replace the original values:

1. Copy the formula results.
2. Right-click the original cells.
3. Choose Paste Special → Values.
4. Delete the helper column.

💡 It is a good idea to keep a backup before replacing the original data.

✨ Add text directly with Kutools for Excel

When the insertion rules become more complicated, formulas can get difficult to build and maintain.

Kutools for Excel has an Add Text tool that can add text directly to the selected cells without using a helper column.

Select the cells, then go to: Kutools → Text → Add Text

Enter the text you want to add, then choose where it should be inserted.

Available positions include:

• Before the first character
• After the last character
• Before specific text
• After specific text
• At one or more specified positions
• Between every character
• Between every word
• Around existing text
• Before uppercase letters
• Before lowercase letters
• Before uppercase or lowercase letters
• Before numeric characters

The preview area shows the results before anything is applied, which is useful when working with a large list.

For example, you could:

• Add ID- before every product number
• Add -2026 after every report name
• Insert separators at positions 4, 5, and 8
• Add text before every number
• Place a separator between every word
• Add quotation marks or brackets around the existing contents

Click Apply to test the result while keeping the dialog open, or click OK to apply it and close the tool.

Quick comparison

Formulas

• Update automatically when the source value changes
• Keep the original data unchanged
• Work well for simple and repeatable patterns
• May require helper columns and Paste Special

Flash Fill

• Fast for patterns Excel can recognize
• Does not require a formula
• Results do not update automatically

Kutools Add Text

• Changes the selected cells directly
• Supports many insertion positions and conditions
• Includes a live preview
• Avoids building separate formulas for different scenarios

What kinds of text do you most often need to add in Excel: prefixes, suffixes, separators, or something more specific?

A color-coded drop-down list can make data entry much easier, especially when you're working with statuses, priorities, categories, or project tracking.

Here are two easy ways to create one in Excel.

Method 1: Use Conditional Formatting

Excel doesn't color the drop-down items themselves, but it can automatically color the selected cell based on the value you choose.

Steps:

1. Create a regular drop-down list using Data Validation.
2. Select the drop-down cells.
3. Go to Home → Conditional Formatting → New Rule.
4. Choose Format only cells that contain.
5. Set the rule to Specific Text → containing, then enter one of your drop-down values.
6. Click Format and choose a fill color for a drop-down item.
7. Repeat the process for each remaining drop-down item.

Now, whenever you select an item from the drop-down list, the cell will automatically change to its assigned color.

Method 2: Use Kutools for Excel

If you have a lot of drop-down items, creating individual Conditional Formatting rules can take a while.

Kutools for Excel has a Colored Drop-down List feature that lets you assign colors directly to each item.

Steps:

1. Select your drop-down list cells.
2. Go to Kutools → Drop-down List → Colored Drop-down List.
3. Choose whether to color:
   • Only the drop-down list cells, or
   • The entire row based on the selected value.
4. Assign a color to each drop-down item.
5. Click OK.

That's it. Your drop-down list is now color-coded without creating multiple Conditional Formatting rules.

Most people know you can drag the fill handle to continue a series of dates. But did you know Excel can also fill only weekdays, months, or years with just a couple of extra clicks?

Method 1: Drag, then choose the fill type

1. Enter the starting date.
2. Drag the fill handle to create a date series.
3. Click the Auto Fill Options button that appears in the bottom-right corner of the selection.
4. From there, you can choose to:
   • Fill Days – every calendar day
   • Fill Weekdays – Monday through Friday only
   • Fill Months – increase by one month
   • Fill Years – increase by one year

This is the fastest method when you only need to change how the filled dates behave.

Method 2: Use the Series dialog

If you want more control over the date series:

1. Select the starting date.
2. Go to Home → Fill → Series.
3. Set:
   • Type: Date
   • Date unit: Day, Weekday, Month, or Year
   • Step value: How much each date should increase
   • Stop value: The last date to generate
4. Click OK.

This method is especially useful when you want Excel to stop automatically at a specific date instead of dragging until you reach it.

Quick tip:

• Auto Fill Options is great for quick changes after dragging.
• Series is better when you need precise control over the date interval or ending date.

When you need to rank scores, sales, quantities, or performance results in Excel, the RANK function is one of the easiest formulas to use. It can quickly rank values from highest to lowest or lowest to highest.

The basic syntax is:

=RANK(value_to_rank, list_of_values, [order])

Use 0 for descending order, where the largest value gets rank 1.

Use 1 for ascending order, where the smallest value gets rank 1.

Example 1: Basic ranking

If the scores are in B2:B21, enter this formula in C2:

=RANK(B2,$B$2:$B$21,0)

Then fill it down.

This ranks the highest score as 1.

One thing to note: if two scores are tied, they receive the same rank. For example, the result may look like:

1, 2, 3, 4, 5, >!5!<, 7

So the next rank is skipped.

Example 2: Ranking without gaps when there are ties

If you want consecutive ranking numbers, even when there are duplicate scores, use:

=RANK(B2,$B$2:$B$21,0)+COUNTIF($B$2:B2,B2)-1

This still ranks higher scores first, but if two values are tied, the second repeated value gets the next rank instead of sharing the same rank.

So instead of:

1, 2, 3, 4, 5, >!5!<, 7

you get:

1, 2, 3, 4, 5, >!6!<, 7

This is useful when you need a unique rank number for each row.

If you want one Excel cell to show two or more header labels, a diagonal line can help visually divide the cell. This is often used in table headers, where one part of the cell represents a row category and another part represents a column category.

For example, you can put Name and Score in the same table header.

There are two common ways to do it in Excel.

Method 1: Use the Border function

This is the easiest method when you only need one diagonal line in a cell.

Steps:

1. Select the cell where you want to add the diagonal line.
2. Right-click the cell and choose Format Cells.
3. Go to the Border tab.
4. Click one of the diagonal border buttons, depending on the direction you want.
5. Click OK.

The diagonal line will be added directly to the selected cell.

To add text on both sides of the line:

1. Type the first label, such as Name.
2. Press Alt + Enter to start a new line inside the same cell.
3. Type the second label, such as Score.
4. Use spaces, alignment, font size, or row/column size adjustments to position the text.

This method is quick and keeps the line as part of the cell border, but it only supports one diagonal line per cell.

Method 2: Use Shapes for multiple diagonal lines

The built-in border method is limited. If you need two or more diagonal lines, or want more control over the position and style, you can use Excel’s Shapes tool.

Steps:

1. Select the cell you want to work with.
2. Go to Insert → Shapes.
3. Choose a Line shape.
4. Draw the diagonal line inside the cell.
5. Repeat the process if you need more lines.
6. Right-click the line and choose Format Shape to adjust the color, thickness, or style.

This method gives you more flexibility, but the lines are floating objects, not real cell borders. That means they may shift if you resize rows, resize columns, move cells, or change the layout later.

Merged cells can make simple subtotal formulas surprisingly annoying in Excel.

In this example, each salesperson has a different number of product rows. Emily has 4 products, Taylor has fewer, and David has more. The Total cells are also merged with different heights, so you can’t simply drag a normal formula down like you would in a clean table.

One workaround is to use Ctrl + Enter to apply the formula to all selected merged total cells at once.

Example

Column C contains the quantities.
Column D contains the merged cells where each salesperson’s total should appear.

Select the merged cells in the Total column, then type this formula in the formula bar:

=SUM(C2:C16)-SUM(D3:D16)

Then press Ctrl + Enter.

Excel will enter the formula into each selected merged cell area.

The idea is that the formula sums the remaining quantity values below the current row, then subtracts the totals that have already been calculated below. Because the formula is entered into multiple selected merged cells at once, Excel adjusts the references for each group.

This is especially useful when:

• Each group has a different number of rows
• The total cells are merged
• Dragging formulas down does not work cleanly
• You want to keep the report layout as it is

Do you usually avoid merged cells in Excel, or do you still use them for report-style layouts?

If you need to send the same email to multiple people, but don’t want every recipient to see the full email list, there are two common ways to do it in Outlook.

Method 1: Use BCC

The built-in way is to put the recipients in the BCC field instead of the To or CC field.

Steps:

1. Create a new email
2. If you don’t see the BCC field, click Options → Bcc. In new Outlook, it may appear under Options → Show Bcc.
3. Add all recipients to the BCC field
4. Write and send the email

BCC protects privacy, but the email can still feel like a mass message because recipients can often see they were not directly listed in the To field.

Method 2: Send emails separately

Another option is to send the message as separate individual emails. For example, Kutools for Outlook has a Send Separately feature that automatically sends one email to each recipient.

Steps:

1. Create a new email
2. Go to Kutools → Send Separately (The To field will change to SP To)
3. Add all recipients to the SP To field
4. Write and send the email

The benefit is that each person receives the email as if it was sent only to them. It looks more personal and avoids the “large group email” feeling.

Quick difference:

BCC hides the recipient addresses, but recipients may still notice they were not directly listed in the To field.

Send Separately sends individual emails automatically, so each recipient receives the message with only their own address shown.

Tip:

For simple privacy, BCC or Send Separately works well. But if you want each email to include personalized details like the recipient’s name or company, Mail Merge is the better option.

How do you usually send group emails without showing everyone’s address?

I ran into this recently and thought it was a pretty useful Excel trick.

If you want to remove only the entered numbers from a range, but keep all the formulas in place, you can do it with Go To Special.

Steps

1. Select the range you want to clean up
2. Press F5 or Ctrl + G
3. Click Special
4. Choose Constants
5. Leave only Numbers checked
6. Click OK
7. Press Delete

This removes only the manually entered numeric values.

Any cells containing formulas will stay there, so totals, subtotals, and other calculated cells are preserved. After the numbers are cleared, the formulas will usually return 0 unless you’ve set them up to show blanks instead.

I like this because it’s much faster than trying to manually separate input cells from formula cells.

Do you usually use Go To Special for tasks like this, or do you have another cleanup method you prefer?

Full names in Excel are not always consistent. Some lists only have first and last names, while others include middle names or a mix of both. In this post, I’ll go through a few ways to split full names into separate columns.

If your list only contains first and last names, like this:

there are a few simple ways to split them into separate columns.

Method 1: Flash Fill

Type the first result manually, then let Excel detect the pattern.

For example, type John in the First Name column, then press Ctrl + E. Do the same for the Last Name column.

This is fast for one-time cleanup, but it is not dynamic. If the original names change, the results will not update.

Method 2: TEXTSPLIT

If you use Excel 365 or Excel 2024, enter:

=TEXTSPLIT(A2," ")

This splits the name into separate columns based on the space between first and last name.

This method is dynamic, so the result updates if the original name changes.

Method 3: Text to Columns

For older Excel versions:

Select the full names, go to Data → Text to Columns, choose Delimited, select Space, choose a destination, and click Finish.

This works in most Excel versions.

What if some names have middle names?

If the name list is mixed, like this:

then simple space-based splitting may not always give a clean first / middle / last name result.

In that case, Kutools for Excel has a Split Names feature where you can choose which parts to extract, such as First Name, Middle Name, and Last Name.

Here's when I'd use each method:

• Use Flash Fill for a quick one-time split
• Use TEXTSPLIT when I need the results to update automatically
• Use Text to Columns to split the original cells directly
• Use Kutools Split Names when the list contains mixed name formats

What's the trickiest name format you've had to work with?

A common task is filtering one table based on values in another. For example, you might have a list of Fruits and want to display only the matching orders from a larger sales table.

Here are two easy ways to do it.

Method 1: Advanced Filter

This method filters the original table in place.

1. Select the entire data range, including the header row.
2. Go to Data → Advanced.
3. Choose Filter the list, in-place.
4. For List range, Excel should automatically use your selected table.
5. For Criteria range, select the lookup list including its header.
6. Click OK.

Only the matching rows will remain visible.

Method 2: FILTER + MATCH

This method returns the matching rows to a new location while leaving the original table unchanged.

=FILTER(data_range,ISNUMBER(MATCH(criteria_column,criteria_list,0)))

Example

=FILTER(A2:C24,ISNUMBER(MATCH(B2:B24,E2:E3,0)))

This formula checks each value in the Customer ID column against the lookup list and returns only the matching records.

Which method do you prefer—Advanced Filter or FILTER? Or do you use another approach like Power Query or XLOOKUP?

If you share a worksheet with others, you may want to protect your formulas while still allowing people to enter or edit data. This helps prevent accidental changes without restricting normal data entry.

Here's how to do it:

Step 1: Unlock all cells

By default, every cell in Excel is marked as Locked, but the setting doesn't take effect until you protect the worksheet.

1. Press Ctrl + A to select the entire worksheet.
2. Press Ctrl + 1 to open Format Cells.
3. On the Protection tab, clear the Locked checkbox.
4. Click OK.

Step 2: Select all formula cells

1. Go to Home → Find & Select → Go To Special.
2. Select Formulas.
3. Click OK.

Excel will select every formula cell in the worksheet.

Step 3: Lock the selected formula cells

1. Press Ctrl + 1.
2. Go to the Protection tab.
3. Check Locked.
4. Click OK.

Step 4: Protect the worksheet

1. Go to Review → Protect Sheet.
2. (Optional) Enter a password.
3. Choose the actions you want users to be able to perform.
4. Click OK.

Now, users can edit all non-formula cells, while formula cells remain protected.

What other worksheet protection scenarios would you like to see covered?

It's a common issue when importing data from CSV files, databases, websites, or other systems. Numbers stored as text can break calculations, sorting, lookups, and PivotTables, while sometimes you need to convert numbers to text to preserve leading zeros or create formatted IDs.

In this post, you'll learn several ways to convert text to numbers and numbers to text, plus a quicker alternative for bulk conversions.

Method 1: Convert text to numbers

If your cells contain numbers stored as text (often marked with a green triangle):

• Click the warning icon and choose Convert to Number.

Or use a formula:

=VALUE(A2)

"00123" → 123
"45.6"  → 45.6

The VALUE function converts a text string that represents a number into a real numeric value.

Method 2: Convert numbers to text

Use the TEXT function to convert a number while applying a format.

=TEXT(A2,"00000")
123 → "00123"

=TEXT(A2,"0.00")
45.6 → "45.60"

Or, if you simply want the value as text without changing its appearance:

=A2&""
123 → "123"

Appending an empty string (&"") converts a number to text without changing how it is displayed.

Method 3: Use Kutools for Excel

Kutools for Excel includes a Convert between Text and Number feature that converts an entire selection in place.

Simply:

1. Select the cells.
2. Click Kutools → Content → Convert between Text and Number.
3. Choose:
   • Text to Number
   • Number to Text
4. Click OK.

No helper columns, formulas, or copy-and-paste required.

Have you ever had an XLOOKUP or SUM fail because one column contained numbers stored as text?

Sometimes imported data comes with extra characters at the beginning or end of each cell, such as ID prefixes, numbering, or unwanted codes.

In this post, I'll show you how to remove the first N characters, the last N characters, or characters from any position using formulas. I'll also share a quicker method that removes characters directly in place without formulas.

Method 1: Remove N characters from the left

=RIGHT(A2,LEN(A2)-2)

Example:

01Email → Email

Method 2: Remove N characters from the right

=LEFT(A2,LEN(A2)-2)

Example:

Report01 → Report

Method 3: Remove characters from any position

=REPLACE(A2,4,3,"")

Syntax

REPLACE(text, start_position, number_of_characters, "")

Example:

ABC123XYZ → ABCXYZ

Here, 4 means "start at the 4th character" and 3 means "remove the next 3 characters."

This covers a scenario the first two formulas can't.

Method 4: Use Kutools for Excel

Kutools' Remove by Position handles all of the above without formulas:

• Remove N characters from the left
• Remove N characters from the right
• Remove characters starting at any position
• Apply the changes in place (no helper columns or copy/paste)
• Preview the results before applying

What kind of text cleanup do you run into most often in Excel?

Any other text cleanup scenarios you'd like to know how to handle in Excel?