Excel Tips

Run-time error 1004 is Excel’s general refusal: VBA asked Excel to do something with a range, a sheet or a workbook that Excel cannot do at that moment, and the message after the number says which. The usual causes are a Range or Cells call that points at the wrong sheet, selecting on a sheet…

When an Excel macro that used to work suddenly stops, the cause is usually outside the code. Something around it changed: Office started blocking macros in the file, the file was saved as .xlsx and lost its code, a sheet or a file the macro refers to was renamed or moved, a library reference went…

To format phone numbers in Excel all the same way, strip each one down to its digits, drop a leading 1, and rebuild it with TEXT. Once D2 holds just the ten digits, 3125550187, =TEXT(D2,”(000) 000-0000″) returns (312) 555-0187. In Microsoft 365 or Excel 2021, one formula does all three steps. Anything that doesn’t come…

To split an address such as “1200 Maple Grove Ln Apt 4B, Springfield, IL 62704” into four columns, count the commas from the right. In Microsoft 365 or Excel 2024, =TRIM(TEXTBEFORE(B2, “,”, -2)) returns the street, =TRIM(TEXTAFTER(TEXTBEFORE(B2, “,”, -1), “,”, -1)) the city, =TEXTBEFORE(TRIM(TEXTAFTER(B2, “,”, -1)), ” “) the state and =TEXTAFTER(TRIM(TEXTAFTER(B2, “,”, -1)), “…

To find invalid email addresses in Excel, put a check formula beside the column and filter for FALSE. In Microsoft 365, =REGEXTEST(TRIM(B2),”^[^@\s]+@[^@\s.]+(\.[^@\s.]+)+$”) returns FALSE for an address with no @ or more than one, a space inside it, or a domain with no dot or a double dot. A format check can’t see typos, though:…

To convert a quote to an invoice in Excel, build the invoice from the quote instead of retyping it: one workbook with a Quote sheet and an Invoice sheet in the same layout, the invoice’s lines pointing at the quote’s with =IF(Quote!A7=””,””,Quote!A7), and a deposit line worked out from the total with =ROUND(D14*B15,2). Once the…

To track overdue invoices in Excel, keep payments in a table of their own, and let formulas work out each invoice’s paid amount, balance, status and days late. Add up each invoice’s payments with =SUMIF($K$2:$K$50,A2,$L$2:$L$50), and count the days late only while a balance is left: =IF(AND(F2>0,$R$1>D2),$R$1-D2,””), with today’s date in R1. A payment is…

To charge sales tax on parts but not labor in Excel, mark every invoice line Taxable, Yes or No, and tax only the Yes lines. Add them up with =SUMIF(E2:E5,”Yes”,D2:D5) and multiply by your rate, kept in a cell of its own: =ROUND(H3*H1,2). Labor marked No is never taxed, whatever else changes on the invoice.…

To invoice materials and labor in Excel, put them on the same invoice as separate lines. Price each material from what it cost you, with a markup (=ROUND(C3*(1+E3),2)) or a margin (=ROUND(C2/(1-E2),2)). Bill labor as hours times your rate. Add a Taxable column and charge tax only on the lines marked Yes, with =SUMIF(H2:H5,”Yes”,G2:G5). Then…

To add a button that runs a macro in Excel, go to the Developer tab, click Insert, and choose the Button under Form Controls (the top group, not ActiveX). Draw it on the sheet and Excel immediately asks which macro to assign. Pick one and click OK. The button runs that macro on a single…

To loop through rows in VBA without Excel freezing, read the whole range into an array first, do the work in memory, then write the results back in one go. Reading and writing the sheet cell by cell is what makes a loop slow: each individual cell access crosses between VBA and Excel, and on…

Selecting a cell before working with it is the single biggest cause of slow VBA. Work with the Range object directly instead: ws.Range(“A1”).Value = “Total” does the same job as selecting A1 and typing into it, without forcing Excel to move the selection and redraw the screen. On a macro that touches thousands of cells,…

To record a macro in Excel, enable the Developer tab, click Record Macro, perform the steps once, then click Stop Recording. To read the code it produced, press Alt+F11 to open the Visual Basic editor and look in Modules. The recorder writes real VBA, so recording a task and reading the result is the fastest…

Use VLOOKUP to match order numbers, invoice IDs, or any lookup value across two spreadsheets. Step-by-step guide with examples and common fixes.

INDEX/MATCH can look up in any direction, handles multiple criteria, and won’t break when columns change. Step-by-step guide with comparison table.

Three methods to find matches, missing items, and duplicates between two Excel lists. COUNTIF, Conditional Formatting, and INDEX/MATCH approaches.

Use SUMIFS to total sales by region and month, count transactions by category, or build summary reports with multiple conditions.

XLOOKUP searches any direction, has built-in error handling, and returns multiple columns. Complete guide with comparison table.

Fix extra spaces, inconsistent casing, text-formatted numbers, and mixed date formats from CSV and CRM imports using TRIM, PROPER, VALUE, and more.

Four methods to split full names into separate columns: formulas, Flash Fill, Text to Columns, and handling middle names and suffixes.

Use conditional formatting to automatically color-code overdue (red), approaching (yellow), and on-track (green) items based on due dates.

Three methods to find and remove duplicate rows based on specific columns in Excel. COUNTIFS flagging, concatenation keys, and the built-in Remove Duplicates tool.

Six methods to convert text dates to real Excel dates. Covers YYYYMMDD, DD/MM/YYYY, dot separators, date-time stamps, and mixed formats.

Turn hundreds of daily transactions into an auto-updating monthly summary using SUMIFS, COUNTIFS, and AVERAGEIFS. Includes Pivot Table alternative.

Use NETWORKDAYS to count working days excluding weekends and holidays. Includes WORKDAY for due dates and custom weekend schedules.

Build running totals that accumulate within each month and reset on the first of the next month. Two formula approaches plus category breakdowns.

Calculate percentage change, year-over-year growth, and CAGR correctly in Excel. Handles zero baselines, negative numbers, and visual indicators.

Use CONVERT for weight, distance, and temperature. Build rate tables for currencies. Format phone numbers, pad zeros, and handle mixed-unit columns.

Three ways to count distinct values: UNIQUE+COUNTA for Excel 365, SUMPRODUCT for all versions, and Pivot Table Distinct Count.

Use conditional formatting to color entire rows based on status, amount thresholds, or multiple conditions. Includes zebra striping and combined rules.