YOUR EVERYDAY DATA DESK

Workspace.

Tools, formulas and worked examples worth keeping close.

Files stay in your browser0 saved on this browser

COPY, CHECK, ADAPT

Start with a formula you can explain.

Each example includes source cells, an expected result and a reason to be careful.

18 results

English function names and comma argument separators are shown. Some locales use semicolons between arguments. Check the app and version notes before copying.

Text cleanup

Excel

Remove pasted spaces that stop names from matching

=TRIM(SUBSTITUTE(B2,UNICHAR(160)," "))

Example & expected result

Enter text in B2:B4: B2 = ' North Harbor '; B3 = 'North Harbor' (the middle space is U+00A0); B4 = 'North Harbor ' (the final space is U+00A0). Enter the formula in C2 and fill through C4. The single quotation marks here describe text; do not type them.

Expected

C2:C4 each contain the text North Harbor, with one ordinary space and 12 characters.

Excel 2016–2024 / Microsoft 365. SUBSTITUTE converts nonbreaking spaces before TRIM removes outer ordinary spaces and collapses repeated ordinary spaces. This does not remove every Unicode or zero-width character. Keep the source column: repeated spaces may be meaningful in codes.

Worked guide Official reference ↗

Text cleanup

Excel

Build a five-digit code from a documented width rule

=TEXT(A2,"00000")

Example & expected result

A2 contains the number 123 and A3 contains 456. Enter the formula in B2 and fill to B3. The source specification must say that every code has exactly five digits.

Expected

B2 is the text 00123; B3 is the text 00456. These are text results, not merely a numeric display format.

Excel 2016–2024 / Microsoft 365. Use only for whole-number codes with a known width. TEXT does not discover how many zeros were originally present, restore digits lost to numeric precision, or truncate longer values to five characters. Keep quantities that need arithmetic numeric.

Worked guide Official reference ↗

Compare and look up

Excel

Check whether a long ID matches the original text

=EXACT(A2,B2)

Example & expected result

Set A2:B2 to Text before entering either ID. A2 contains 1234567890123456 from an independently preserved source; B2 initially contains the same 16-character text. Enter the formula in C2. Then change only B2 to the text 1234567890123457.

Expected

C2 is TRUE for the identical text and FALSE after the last character in B2 changes.

Excel 2016–2024 / Microsoft 365. EXACT compares case and spaces as well as digits. Text formatting after numeric import cannot restore lost digits. TRUE is useful only when the reference itself preserves the original; two equally damaged copies can still match.

Worked guide Official reference ↗

Numbers

Excel

Convert text amounts without turning blanks into zero

=IF(B2="","",NUMBERVALUE(B2,".",","))

Example & expected result

B2:B5 contain text values 12.50, 7.25, -2.00, and 0; B6 is empty. Enter the formula in C2 and fill through C6.

Expected

C2:C5 contain the numbers 12.5, 7.25, -2, and 0. C6 displays blank. SUM(C2:C6) is 17.75 and COUNT(C2:C6) is 4.

Excel 2016–2024 / Microsoft 365. This source uses a decimal point and comma grouping. Preserve IDs as text. The blank branch returns empty text, not a physically empty cell. Investigate conversion errors instead of replacing them with zero. Some locales require semicolons between arguments; keep the quoted separators as shown.

Worked guide Official reference ↗

Numbers

Excel

Read decimal-comma amounts from an export

=NUMBERVALUE(B2,",",".")

Example & expected result

B2:B4 contain text 1.234,50; 25,75; and -10,25. Enter the formula in C2 and fill to C4.

Expected

C2:C4 are numeric 1234.5, 25.75, and -10.25; their sum is 1250.

Excel 2016–2024 / Microsoft 365. Confirm that commas are decimal separators and periods are grouping separators in this source. An empty string becomes zero with this unguarded formula; use the blank-preserving card when blanks exist. Do not replace punctuation across an entire mixed-data file.

Worked guide Official reference ↗

Dates and time

Excel

Turn known day/month/year text into sortable dates

=DATE(VALUE(RIGHT(B2,4)),VALUE(MID(B2,4,2)),VALUE(LEFT(B2,2)))

Example & expected result

B2:B4 contain the exact text 02/10/2026, 15/02/2026, and 31/12/2025. Enter the formula in C2 and fill to C4; format C2:C4 as yyyy-mm-dd. In D2 enter =IFERROR(TEXT(C2,"dd/mm/yyyy")=B2,FALSE) and fill to D4 as a round-trip check.

Expected

C2:C4 display 2026-10-02, 2026-02-15, and 2025-12-31, stored as numeric dates. D2:D4 are TRUE. Ascending date order is C4, C3, C2.

Excel 2016–2024 / Microsoft 365. Only use for confirmed fixed-width DD/MM/YYYY text. DATE can roll impossible dates into the next month; accept a converted row only when the round-trip check passes. This formula cannot infer whether an ambiguous source intended month/day/year.

Worked guide Official reference ↗

Dates and time

Excel

Remove the time from a numeric timestamp

=INT(B2)

Example & expected result

In B2 enter =DATE(2026,1,2)+TIME(18,0,0). Put the card formula in C2, then format C2 as yyyy-mm-dd.

Expected

C2 displays 2026-01-02 with no time fraction. In a workbook using the 1900 date system, its underlying number is 46024; B2 is 46024.75.

Excel 2016–2024 / Microsoft 365. This example requires a positive numeric Excel timestamp. INT changes the value; a date display format alone would keep the time. It does not parse date text or correct a 1900/1904 date-system mismatch in imported raw serials.

Worked guide Official reference ↗

Dates and time

Excel

Keep only the time of day from a timestamp

=MOD(B2,1)

Example & expected result

In B2 enter =DATE(2026,1,2)+TIME(18,0,0). Put the card formula in C2 and format C2 as hh:mm:ss.

Expected

C2 contains 0.75 of a day and displays 18:00:00. Multiplying C2 by 24 produces the number 18.

Excel 2016–2024 / Microsoft 365. Use a positive numeric timestamp. The formula discards whole days, so it is unsuitable for preserving an elapsed duration over 24 hours. It neither parses text nor converts time zones.

Worked guide Official reference ↗

Summaries

Excel

Include the entire last day in a date-range total

=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<"&(F2+1))

Example & expected result

A2:A8 must be numeric timestamps, entered respectively as =DATE(2026,8,31)+TIME(23,59,0); =DATE(2026,9,1); =DATE(2026,9,15)+TIME(12,30,0); =DATE(2026,9,30); =DATE(2026,9,30)+TIME(18,45,0); =DATE(2026,10,1); =DATE(2026,9,20)+TIME(9,0,0). C2:C8 contain numbers 5, 10, 20, 30, 40, 50, 60. Set E2 to =DATE(2026,9,1), F2 to =DATE(2026,9,30), and put the card formula in H2.

Expected

H2 is 160. The September 30 evening amount is included, while August 31 and October 1 are excluded.

Excel 2016–2024 / Microsoft 365. E2 and F2 must be whole dates with start no later than end. Using less than the next day includes end-day timestamps; using less than or equal to midnight would miss later times. Keep all ranges the same size and amounts numeric. No time-zone conversion occurs.

Worked guide Official reference ↗

Summaries

Excel

Show a total that follows visible rows

=SUBTOTAL(109,C2:C7)

Example & expected result

B1:C7 has headers Region and Amount, followed by North/100, South/200, North/30, North/40, South/50, North/60. Put the formula in F12, outside the data rows. Filter B1:C7 to Region = North. As a separate test, clear the filter and manually hide worksheet row 5, containing 40.

Expected

With all rows visible the total is 480. Filtering to North gives 230. With no filter and only worksheet row 5 manually hidden, it is 440.

Excel 2016–2024 / Microsoft 365. Function number 109 sums while excluding filtered-out and manually hidden rows. Use numeric amounts and a vertical range; hiding columns is different. Keep the summary outside the filtered data rows so the summary itself remains visible.

Worked guide Official reference ↗

Dates and time

Excel

Count working days, including eligible endpoints

=NETWORKDAYS(A2,B2,$H$2:$H$3)

Example & expected result

A2 =DATE(2026,9,4); B2 =DATE(2026,9,11); H2 =DATE(2026,9,7); H3 =DATE(2026,9,10). H2:H3 are invented office-closure dates for this example. Put the formula in C2.

Expected

C2 is 4: September 4, 8, 9, and 11. Both endpoints are counted because they are eligible weekdays.

Excel 2016–2024 / Microsoft 365. NETWORKDAYS excludes Saturday, Sunday, and the numeric dates you supply as holidays. It does not fetch a national holiday calendar or count work hours. Validate date order for a forward schedule; reversed endpoints produce a negative count.

Worked guide Official reference ↗

Dates and time

Excel

Count working days when Friday and Saturday are off

=NETWORKDAYS.INTL(A2,B2,"0000110",$H$2:$H$3)

Example & expected result

A2 =DATE(2026,9,4); B2 =DATE(2026,9,11). H2 =DATE(2026,9,7) and H3 =DATE(2026,9,10) are invented closures. Enter the formula in C2. Keep the seven-character weekend mask inside quotation marks.

Expected

C2 is 3: September 6, 8, and 9. Friday and Saturday are excluded, but Sunday is eligible.

Excel 2016–2024 / Microsoft 365. The mask runs Monday through Sunday; 1 marks an excluded weekday. The text mask is different from numeric weekend codes. Use numeric dates and an explicit holiday list; there is no automatic holiday lookup. Check start/end order.

Worked guide Official reference ↗

Compare and look up

Excel

Label IDs that are missing from a second list

=IF(A2="","Skip blank",IF(SUMPRODUCT(--($D$2:$D$7=A2))=0,"Only in A","In both"))

Example & expected result

A2:A8 contain A101, A102, A102, A103, ' A104' (one leading space), CaseA, and an empty cell. D2:D7 contain A102, A103, A104, casea, A105, and an empty cell. Put the formula in B2 and fill through B8. Do not type the descriptive single quotation marks.

Expected

B2:B8 show Only in A; In both; In both; In both; Only in A; In both; Skip blank.

Excel 2016–2024 / Microsoft 365. Equality here treats * and ? literally and ignores letter case, while retaining spaces. This checks presence, not duplicate counts or paired row changes. Keep both ID columns as text when zeros or long digits matter, and extend the bounded comparison range for your actual data.

Worked guide Official reference ↗

Compare and look up

Excel

Flag duplicates only when both record fields match

=AND($A2<>"",$B2<>"",SUMPRODUCT(--($A$2:$A$9=$A2),--($B$2:$B$9=$B2))>1)

Example & expected result

A1:B9 has headers Order and SKU, then O-01/P-A, O-01/P-B, O-01/P-A, O-02/P-A, O-03/P-C, O-03/P-C, [empty]/P-C, O-04/[empty]. Enter the formula in C2 and fill through C9. For highlighting instead, use it as a conditional-formatting formula with active cell A2 and Applies to =$A$2:$B$9.

Expected

TRUE appears on worksheet rows 2, 4, 6, and 7. Rows 3, 5, 8, and 9 are FALSE. Both the first and later occurrences of each complete duplicate pair are flagged.

Excel 2016–2024 / Microsoft 365. Matching is case-insensitive and uses literal characters, including * and ?. Both fields must be populated; an unflagged incomplete row is not automatically valid. A flag identifies review candidates, not permission to delete. Keep fixed comparison ranges and moving row references distinct.

Worked guide Official reference ↗

Compare and look up

Excel

Look up a price on another sheet and label missing IDs

=IF(A2="","",IFNA(VLOOKUP(A2,'Price List'!$A$2:$C$6,3,FALSE),"Not found"))

Example & expected result

Create a sheet named Price List. A1:C6 has headers ID, Item, Price and rows P101/Lamp/18, P102/Tray/0, P103/Hook/7, P103/Hook/8, P104/Stand/[empty]. On another sheet, A2:A7 contain P101, P103, P999, [empty], P102, P104. Enter the formula in B2 and fill to B7.

Expected

B2:B7 show 18; 7; Not found; blank; 0; 0. P103 returns the first match. The empty P104 price also returns zero, so it must not be mistaken for a confirmed free item.

Excel 2016–2024 / Microsoft 365. FALSE requests exact lookup matching; keys must be in the first column of the table. Validate duplicates and blank source prices. IFNA also masks a source #N/A returned by a matched cell; it is not proof that the key is absent. Other errors remain visible. The input blank branch returns empty text.

Worked guide Official reference ↗

Split and deduplicate

Google Sheets

Split simple delimited text without shifting empty fields

=SPLIT(A2,",",FALSE,FALSE)

Example & expected result

A2 contains the plain text U1,,Seoul,active. Clear B2:E2, then enter the formula in B2.

Expected

B2:E2 contain U1, an empty second field, Seoul, and active. Seoul remains in the third field.

Google Sheets, web version. The final FALSE preserves empty fields between consecutive delimiters; the preceding FALSE treats the delimiter as a complete string. Leave room for the output. This is not a CSV parser: quoted commas or embedded line breaks require a proper CSV import. Locale settings may require semicolons between arguments.

Worked guide Official reference ↗

Summaries

Google Sheets

Total amounts by region and category

=QUERY(A1:C8,"select A, B, sum(C) where A is not null group by A, B order by A, B label A 'Region', B 'Category', sum(C) 'Total'",1)

Example & expected result

A1:C8 has headers Region, Category, Amount and rows East/Books/40, East/Books/15, East/Games/30, West/Books/20, West/Games/25, West/Games/5, [empty]/Books/9. Amounts are numbers. Clear E1:G5 and enter the formula in E1.

Expected

E1:G5 contains the headers Region/Category/Total followed by East/Books/55, East/Games/30, West/Books/20, West/Games/30. The grouped total is 135. The source total is 144; the 9 with no region is intentionally excluded.

Google Sheets, web version. The final 1 declares one header row. QUERY treats minority data types in a mixed column as null, so validate numeric amounts before grouping. The nonempty-region condition is an explicit exclusion policy, not a repair for missing data. Output cells must be available.

Worked guide Official reference ↗

Split and deduplicate

Google Sheets

Preview unique complete rows without deleting the source

=UNIQUE(A2:C6)

Example & expected result

A1:C6 has headers Member ID, Name, City and rows E01/Ada/Seoul, E02/Ben/Busan, E01/Ada/Seoul, E01/Ada/Busan, E03/Cara/Daegu. Clear E2:G5 and enter the formula in E2.

Expected

E2:G5 returns E01/Ada/Seoul, E02/Ben/Busan, E01/Ada/Busan, and E03/Cara/Daegu. The first occurrence order is retained; the same ID with a different city remains a separate row.

Google Sheets, web version. UNIQUE compares all three selected columns. It does not choose the latest record per ID or resolve conflicting cities. Hidden spaces can preserve apparent duplicates. Leave room for the expanding output and extend the bounded source range when records are added.

Worked guide Official reference ↗

Data tools run locally. Saved items contain links only. Refreshing or leaving a tool can clear your working data. How your data is handled

Bookmark this workspace: Ctrl / ⌘ + D

Want to understand the spreadsheet behind the task? Learn step by step in Excel Academy ↗