DEV Community: HODADAK

Excel Conditional Formatting: Highlight Overdue Dates Automatically

HODADAK — Wed, 14 Oct 2026 10:30:00 +0000

Coloring overdue rows by hand works until you miss one. It also has to be redone every day, because yesterday's "due tomorrow" is today's "due today" and tomorrow's "late."

TL;DR: Select the due-date cells, add a Highlight Cells Rules > Less Than rule, and use =TODAY() as the value.

Steps

Select the cells with due dates. Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than.
In the value box, enter =TODAY() and confirm. Only dates earlier than today turn red.
Edit a due date and the color updates immediately. There is nothing to repaint.

Because the rule compares against today's date, cells that become overdue tomorrow will turn red tomorrow without you touching the file.

Checked in the free Excel for the web.

Where it fits

Task trackers, invoice due dates, contract renewal lists, document review deadlines. Anywhere a column of dates answers the question "is this late?", this rule answers it visually.

Things that trip people up

Empty cells in the selected range can be treated as zero, which is earlier than today, so they may light up too. Select only the rows that contain dates, or add a separate rule for blanks.

Dates typed as text (for example with an extra space or a format Excel didn't recognize) are not real dates, and the comparison won't work reliably on them. If one row refuses to change color, check whether that cell is actually a date.

Use =TODAY(), not a typed date. A fixed date in the rule means the highlighting freezes on that day.

The same rule from Python

If you generate the workbook with openpyxl, you can attach the rule in code so every exported file already has it:

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

red = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add(
    "C2:C50",
    CellIsRule(operator="lessThan", formula=["TODAY()"], fill=red),
)

The rule is stored in the file and evaluated by Excel when the workbook is opened, so the result is the same as setting it up by hand. Coloring cells in the script itself would only be correct on the day the file was generated.

AI helped draft this article. The rule was set up and tested in Excel for the web.

ChatGPT Excel Formulas: Four Details to Put in Your Prompt

HODADAK — Wed, 14 Oct 2026 03:10:00 +0000

"Give me an Excel formula for this" tends to get a generic answer. The model has to guess which columns hold what, how many rows there are and where the formula will go, so you end up rewriting ranges by hand or going back and forth several times.

TL;DR: Tell it where the data is, what you want calculated, which cell the formula goes in and which Excel you use. Then test the formula on a small table before trusting it.

The four details

Table layout. Say which column holds what and which rows the data covers. Example: "Column A is name, B is department, C is region, D is sales, rows 2 to 26."
The result you want. One sentence with the conditions and the calculation (sum, count or average). Example: "total sales for people whose department is Sales and region is Seoul."
The result cell. Example: "one formula I can put in H12." Naming the cell makes the answer something you can paste directly.
Your environment. Example: "Excel for the web." Available functions differ between versions, so this keeps you from getting a function your Excel doesn't have.

Put together, the prompt reads:

Column A is name, B is department, C is region, D is sales (rows 2 to 26). Give me one formula for H12 that sums sales where department is Sales and region is Seoul. I'm using Excel for the web.

A correct answer for that prompt looks like this:

=SUMIFS(D2:D26,B2:B26,"Sales",C2:C26,"Seoul")

The same SUMIFS pattern was checked in Excel for the web on a sample sheet, and the result matched a total calculated in advance.

Two habits worth keeping

Test before you use it. Build a tiny table where you already know the answer, run the formula on it, and compare. A formula can be syntactically valid and still sum the wrong column.

Describe the data instead of pasting it. Company spreadsheets often contain names, prices or customer details. The model only needs column names and the shape of the table to write a formula, so there is no reason to paste the sheet itself.

A second opinion in Python

If you already work with pandas, a one-line cross-check is easy:

df.loc[(df["Department"] == "Sales") & (df["Region"] == "Seoul"), "Sales"].sum()

If the pandas number and the Excel number match, you can be fairly confident the formula's conditions are right. If they don't, the difference usually points to a wrong range or a typo in a condition value.

This article was created with the help of AI. The example formula was tested in Excel for the web.

Excel Ctrl+; Shortcut: Insert Today's Date as a Fixed Value

HODADAK — Tue, 13 Oct 2026 22:40:00 +0000

Logs, checklists and request trackers all need a date column. Typing the full date into each cell is about ten keystrokes every time, and it is easy to mistype a digit when you are doing it for the twentieth row.

TL;DR: Ctrl+; enters today's date in the active cell. Select several cells first and finish with Ctrl+Enter to fill all of them.

Steps

Select the cell where the date should go and press Ctrl+; (Ctrl and the semicolon key). Today's date appears in the cell.
Press Enter to confirm.
To fill many cells at once, select them all, press Ctrl+;, then press Ctrl+Enter instead of Enter. Every selected cell gets the same date.

Checked in the free Excel for the web.

Why not =TODAY()?

The shortcut enters a plain date value, not a formula. That difference matters.

=TODAY() recalculates. Open the workbook tomorrow and every cell using it shows tomorrow's date. That is fine for a "days until deadline" calculation, but wrong for a log where you want to record when something happened.

Ctrl+; writes the date once and leaves it alone, the same as if you had typed it.

Common mistakes

Using =TODAY() in a "date received" or "date completed" column is the classic one. Everything looks right on the day you enter it, and then all the dates quietly move.

Another is pressing Enter instead of Ctrl+Enter after selecting a range. Enter only fills the active cell; Ctrl+Enter is what pushes the entry into the whole selection.

How the date looks depends on the cell's number format and your regional settings. If you see a five-digit number instead of a date, the value is fine; change the cell format to a date format.

Doing the same thing in code

If a script is filling the sheet, write a date value, not the TODAY formula, for the same reason as above. With openpyxl:

import datetime
ws["B2"] = datetime.date.today()

In VBA, Range("B2").Value = Date does the same job. Both store a fixed date, which matches what the keyboard shortcut does by hand.

This post was put together with help from AI; the shortcut behavior was checked in Excel for the web.

Word Paste Options: Ctrl+V, Ctrl+Shift+V and Keep Text Only

HODADAK — Tue, 13 Oct 2026 16:16:15 +0000

You copy a paragraph from a web page or a chat window, paste it into a report, and that one section now has a different font, size and color from the rest of the document. Fixing it means reselecting the text and resetting the style by hand, and the problem comes back the next time you paste.

TL;DR: Ctrl+V keeps the original look, Ctrl+Shift+V brings in only the words, and the Paste Options button lets you switch after the fact.

The paste moves

Ctrl+V pastes with the source formatting. Bold, italics and underline come along with the text.
Ctrl+Shift+V pastes the text without its formatting. Use it for anything copied from a website or a messenger, so the document keeps its own styling.
Right after pasting, a small Paste Options button with a (Ctrl) label shows up next to the text. Click it and pick Keep Source Formatting, Merge Formatting or Keep Text Only to change how that paste was applied.

These were checked by pasting into Word for the web.

Which one to reach for

Moving a styled block between two of your own documents is the case where plain Ctrl+V makes sense, because you usually want it to look the same.

For quotes, addresses or notes pulled from a browser, Ctrl+Shift+V saves a cleanup step.

If you are not sure, paste normally and then use the Paste Options button. Switching there is quicker than undoing and pasting again.

The surprise with "text only"

Pasting text only does not mean the text arrives with no formatting at all. It picks up whatever formatting is active at the cursor. If the character right before your cursor is bold, the plain-text paste comes in bold as well. Turn that formatting off first, then paste.

One more thing to know: older desktop versions of Word used Ctrl+Shift+V to paste formatting (the partner of Ctrl+Shift+C) rather than to paste plain text. If the shortcut does something unexpected on your machine, the Paste Options button gives you the same choice.

If you script Word instead

In Word VBA the plain-text paste is a single call:

Selection.PasteSpecial DataType:=wdPasteText

If you generate documents with python-docx, the issue mostly disappears, since each run only carries the formatting you set on it. The manual shortcuts above are for the everyday case where a person is assembling a document from several sources and wants it to look like one file.

This article was created with the help of AI. The steps come from our own test in Word for the web.

File Naming Conventions That Sort by Date: 4 Simple Rules

HODADAK — Tue, 13 Oct 2026 16:15:35 +0000

Every shared folder seems to have a "report_final_REALLY_final.xlsx" somewhere. Nobody is sure which file is current, and sorting by name doesn't help, because the names were never written to be sorted.

A few naming rules fix both problems. They need no software, only agreement on the order of things.

TL;DR: Start with the date as YYYY-MM-DD, use two digits for months and days, use v1 and v2 instead of "final", and keep the order date_content_version.

The problem with casual month numbers

Name monthly files the casual way, starting with the month as a plain number ("9 sales report", "10 sales report" and so on), then sort by name. The order comes out as 10, 11, 8, 9. Names are compared character by character, so "1" comes before "8" no matter what follows it.

This was checked by sorting the names in Excel for the web. With the date first, as 2026-08, 2026-09, 2026-10, 2026-11, the same sort comes out in date order.

The four rules

Date first, as year-month-day. Example: 2026-10-01_sales-report.xlsx. Sorting by name now sorts by date.
Two digits for months and days. 2026-09, not 2026-9. Without the leading zero, September lands after October and November.
Version numbers instead of "final". sales-report_v1, then sales-report_v2. The highest number is the latest, and nobody has to invent a second "final".
A fixed order: date, content, version. Example: 2026-10-01_sales-report_v2.xlsx. When the whole team uses the same order, everyone finds files the same way.

When to bother

Any folder more than one person touches, and any folder you'll be searching six months from now. Reports, invoices, meeting notes and exports benefit most, because they arrive in a series.

Common mistakes

Mixing formats. A folder with half "2026-10-01" and half "Oct 1" sorts into two separate groups. Pick one format and rename the old files once.
Putting the date in the middle. sales-report_2026-10-01 groups by content first. That's fine if it's what you want, but it won't give you a timeline.
Versions past nine. If you expect more than nine versions, v01 to v10 keeps them in order.

Why developers already do this

It's the same reason ISO 8601 dates are standard in logs and backups. A string sort and a date sort agree when the date is written largest unit first with fixed widths. In Python, datetime.date.today().isoformat() gives you a string like 2026-10-01, ready to prefix a filename, and sorted() on those names returns them in date order with no parsing.

This post was written with AI assistance. The sorting example was checked in Excel for the web.

Excel Number Format Shortcuts: Currency, Percent, Time and Borders

HODADAK — Tue, 13 Oct 2026 16:14:53 +0000

Format Cells has every number format you could want, which is exactly why finding the right one takes a while. The formats you use every day have shortcuts, and they're easier to remember once you notice which key each symbol sits on.

TL;DR: Ctrl+Shift+$ currency, Ctrl+Shift+% percent, Ctrl+Shift+@ time, Ctrl+B / I / U, Ctrl+Shift+& outside border.

The shortcuts

Ctrl+Shift+$ applies currency format. In the test sheet, 1234567 became ₩1,234,567 because the sheet used Korean settings; the symbol follows your regional settings. $ is on the 4 key.
Ctrl+Shift+% applies percentage. 0.12 becomes 12%. % is on the 5 key.
Ctrl+Shift+@ applies a time format. 0.5625 displayed as 13:30:00 in the test. Depending on your regional settings, you may see a 12-hour clock with AM/PM instead. @ is on the 2 key.
Ctrl+B makes text bold. Select the header row and press it. Press again to remove it.
Ctrl+I applies italic, for cells you want to stand out.
Ctrl+U underlines. Press again on the same cell to remove it.
Ctrl+Shift+& adds an outside border. Select the table and press it to draw a border around the outside. & is on the 7 key.

Each shortcut was pressed and checked in Excel for the web.

The value doesn't change

Number formats only change how a value looks. A cell holding 0.12 still holds 0.12 after you show it as 12%, and formulas that use it still get 0.12. That's also why 0.5625 can show as 13:30. Excel stores times as fractions of a day, and 0.5625 of 24 hours is 13.5 hours.

Common mistakes

Typing 12 and then formatting as percent. You get 1200%. Type 0.12, or type 12% directly.
Expecting currency format to convert money. It only adds the symbol and separators. No exchange happens.
Hunting for the symbol on another keyboard layout. The shortcuts follow the symbols above 4, 5, 2 and 7 on a US-style layout. On other layouts, check which key produces the symbol.

In code

When you write workbooks with openpyxl, these become format strings on the cell. Percent is cell.number_format = "0%", and a time format looks like "hh:mm:ss". The point about values carries over: write 0.12 and format it, rather than writing the text "12%", or formulas further down the line won't be able to use it.

Drafted with the help of AI and reviewed against the checked shortcut list.

Google Search Operators for Work: site:, filetype:, Quotes, Minus

HODADAK — Tue, 13 Oct 2026 16:14:10 +0000

Searching for a work document often turns up blog posts, ads and paid templates before the official form you actually need. You can keep scrolling and opening tabs, or you can tell the search engine more precisely what you want.

Four operators handle most cases.

TL;DR: site: for one website, filetype: for a file type, quotes for an exact phrase, a minus sign to exclude a word.

The operators

site: searches one website. Example: year-end tax settlement site:nts.go.kr returns results only from nts.go.kr, the site of Korea's National Tax Service. Useful when you know which organization publishes the official answer.
filetype: filters by file type. Example: employment contract filetype:pdf brings PDF documents to the top. Good for forms, specifications and reports.
Quotes match an exact phrase. Example: "travel expense report" template only returns pages where those words appear together, in that order.
A minus sign excludes a word. Example: meeting minutes template -paid drops results containing "paid". Attach the minus directly to the word, with no space.

All four were checked in Google Search. The example queries are translated from the Korean ones used in that check.

Combining them

The operators stack. A query like "expense report" filetype:pdf site:example.org asks for a PDF containing that exact phrase, from one site. Start with one operator and add another only if the results are still noisy.

If Google is your default search engine in Chrome, you can type these straight into the address bar.

Common mistakes

A space after the colon. site: nts.go.kr doesn't behave like site:nts.go.kr. Keep them together.
A space after the minus. - paid is not an exclusion. It has to be -paid.
Quoting too much. A long quoted phrase has to match exactly, so one different word removes good results. Quote the part that must be exact and leave the rest open.

A note for developers

These are the same filters that help when searching documentation or issue trackers. site: with a project's docs domain often beats the site's own search box, and putting an exact error message in quotes is a fast way to find someone who hit the same problem.

Written with AI assistance. The operators and examples come from a checked list.

Word Formatting Shortcuts: 6 Keys for Clean Report Layouts

HODADAK — Tue, 13 Oct 2026 16:13:29 +0000

Laying out a report with the mouse means a lot of trips to the ribbon: select the title, find the center button, select the date, find align right, open the font size list, pick a size, repeat. Each trip is a second or two, and a report has dozens of them.

Six shortcuts cover most of that work.

TL;DR: Ctrl+E, Ctrl+] and Ctrl+[, Ctrl+R, Shift+Enter, Ctrl+B / I / U, Ctrl+Shift+8.

The shortcuts

Ctrl+E centers. On the title line, press it and the title is centered.
Ctrl+] and Ctrl+[ change the size. Select text, then Ctrl+] makes the font one step bigger and Ctrl+[ one step smaller. Handy when you're judging by eye rather than picking a number.
Ctrl+R aligns right. Put the cursor on the date or author line and press it.
Shift+Enter starts a new line in the same paragraph. After the date, it moves to a new line without starting a new paragraph. Because it's still one paragraph, the right alignment carries over to the author name.
Ctrl+B, Ctrl+I and Ctrl+U apply bold, italic and underline. Select and press. Press again to remove it.
Ctrl+Shift+8 shows formatting marks. Empty lines, line breaks and paragraph marks (¶) become visible, so you can see why the layout looks off.

Each one was pressed and checked in Word for the web.

Why Shift+Enter matters

Paragraph settings like alignment and spacing apply to the whole paragraph. Press Enter between the date and the author and you get two paragraphs, possibly with more space between them than you want. Shift+Enter keeps them together as one block, which is usually what a header area needs.

Common mistakes

Using spaces to center or right-align. It looks right until the font or margins change. Alignment survives edits; spaces don't.
Fixing layout blind. When something won't line up, turn on formatting marks first. Stray empty paragraphs and manual line breaks are the usual cause.
Formatting every heading by hand. For long documents, built-in heading styles are a better habit. Change the style once and every heading follows.

If you generate reports from code

python-docx sets the same things as properties. Alignment is paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER, and bold is run.bold = True. Even so, these shortcuts are what you'll use to tidy a generated file before it goes out.

Drafted with AI assistance; the shortcuts come from a list checked in Word for the web.

Excel Table Shortcuts: 7 Keys That Replace Mouse Dragging

HODADAK — Tue, 13 Oct 2026 16:12:47 +0000

Dragging the mouse down a long column to select it usually ends one of two ways: you overshoot into empty rows, or you stop short and start over. Scrolling to find the last row of a table is the same story. None of it is hard, but it adds up over a day of spreadsheet work.

These seven shortcuts cover most of the moving, selecting and filtering people normally do with the mouse.

TL;DR: Ctrl+Down, Ctrl+Shift+Down, Ctrl+Space, Ctrl+Enter, Alt+Enter, Ctrl+Shift+L, Alt+Down.

The shortcuts

Ctrl+Down jumps to the end of the data. From A2, it goes straight to the last filled row (A26 in the example sheet) without scrolling.
Ctrl+Shift+Down selects to the end. From D2, it selects D2 through D26, all 25 cells, in one press.
Ctrl+Space selects the entire column. With C5 active, the whole of column C is selected.
Ctrl+Enter puts the same value in every selected cell. Select E2:E6, type a word such as "OK", then press Ctrl+Enter instead of Enter. All five cells get the value.
Alt+Enter starts a new line inside a cell. While typing, press it and the text continues on a second line in the same cell.
Ctrl+Shift+L turns filters on or off. With the cursor inside the table, each header gets a drop-down arrow. Press again to remove them.
Alt+Down opens a filter list. On a header that has a filter arrow, it opens the list without touching the mouse.

Each one was pressed and checked in Excel for the web.

Where they help most

The first two are the ones people tend to keep. Ctrl+Shift+Down followed by Ctrl+C is the quickest way to copy a full column of data, and it doesn't care whether the table has 25 rows or 25,000. Ctrl+Enter is handy for status columns where many rows need the same label.

Common mistakes

Ctrl+Down stopping early. It jumps to the edge of the current block of data, so a blank cell in the middle of the column makes it stop there. If it lands short, look for a gap.
Pressing Enter instead of Ctrl+Enter. Plain Enter only fills the active cell and moves down. The rest of the selection stays empty.
Pressing Ctrl+Shift+L outside the table. If the active cell is away from your data, Excel may not detect the range you meant. Click inside the table first.

The same moves in code

If you script Excel, these map onto things you already use. In VBA, Range("A2").End(xlDown) is Ctrl+Down. In openpyxl, ws.max_row gives you the last used row, and ws.auto_filter.ref = "A1:E26" adds the filter arrows. When you're working in the workbook by hand, the keys are quicker.

Written with AI assistance and reviewed against the original checked list.

Word Translator: Translate a Selection Without Leaving Word

HODADAK — Tue, 13 Oct 2026 15:55:38 +0000

Translating a paragraph usually goes like this: copy it, open a translator in another tab, paste, copy the result, switch back to the document, paste again. Do that a few times while drafting an email to an overseas colleague and most of the effort goes into moving between windows.

TL;DR: Select the text, then Review > Translate > Translate Selection, and choose the language you want in the Translator pane.

Steps

Select the text you want translated. On the Review tab, click Translate, then Translate Selection.
The Translator pane opens on the right. In the target language box, type the language you want (for example, English) and pick it from the list. The translation appears in the pane straight away.
To get another language, change only the target language, for example to Japanese. The translated text updates without reselecting anything.

The language you choose is remembered for next time, so repeat translations into the same language skip step 2.

Checked in the free Word for the web.

Where it helps

It fits the small jobs: checking what a paragraph from a partner says, drafting a reply in a second language, or comparing how a sentence reads in two languages. You stay in the document the whole time.

Things to watch

Machine translation is a starting point. Read the output before you send it, and pay extra attention to names, numbers, dates and product or legal terms, which are the places where a wrong word causes real trouble.

The text you select is sent to Microsoft's translation service to be translated. If your company has rules about confidential documents and cloud services, check them before translating anything sensitive.

If you want the whole file in another language, desktop Word's Translate menu also has a Translate Document option, which creates a translated copy instead of showing it in the pane.

Compared with scripting it

Developers who need to translate many files usually go through a translation API (Microsoft's own Azure AI Translator is one option) and loop over documents in code. That is the right tool for batch work. For one paragraph in the middle of writing, the built-in pane is faster than any script, because there is nothing to set up.

Written with AI assistance. The steps were tested in Word for the web.

Excel Drop-Down List with Data Validation: Stop Typo Statuses

HODADAK — Tue, 13 Oct 2026 15:54:55 +0000

A task tracker has a Status column, and everyone fills it in their own way. One person writes "Done", another "Completed", a third "Finished". They mean the same thing, but a formula counting "Done" only sees one of them. In the example sheet, three tasks were finished and the count came back as 1.

The fix is to let people pick a value instead of typing it.

TL;DR: Select the status cells, go to Data > Data Validation, allow a List with the source In Progress,Done,On Hold, and confirm.

Steps

Select the status cells, then go to the Data tab and open Data Validation.
Under Allow, choose List. In Source, type the options separated by commas: In Progress,Done,On Hold. Confirm.
Each cell now shows an arrow. Click it and pick a value.
Fix the old entries the same way. In the example, the cells that said "Completed" and "Finished" were switched to "Done" from the list, and the count went from 1 to 3.

Checked in the free Excel for the web.

Why the count was wrong

A count like this looks for an exact match:

=COUNTIF(C2:C20,"Done")

"Completed" and "Finished" aren't "Done", so they aren't counted. The formula was fine; the data wasn't consistent. A drop-down list fixes the input, which fixes every formula and filter that reads it.

Common mistakes

Spaces in the source list. Typing a space after each comma can add a leading space to the options. Leave the spaces out.
Assuming old data gets fixed. Validation applies to new entries. Values typed before you added the list stay as they were, so clean those up separately.
A list that keeps growing. If there are more than a handful of options, put them in a range on another sheet and point Source at that range instead of typing them.

Adding the list from code

If you generate trackers with openpyxl, you can add the same validation before anyone opens the file:

from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"In Progress,Done,On Hold"')
ws.add_data_validation(dv)
dv.add("C2:C100")

Everyone then gets the same arrow and the same three choices, and the counts stay accurate.

AI-assisted post. The steps and the before-and-after count come from a walkthrough checked in Excel for the web.

Word Track Changes: Show Every Edit, Then Accept All

HODADAK — Tue, 13 Oct 2026 15:54:11 +0000

You edit a shared document, send it back, and then explain in a message what you changed: a number on page two, a sentence in the summary, a date at the end. The reviewer has to hunt for each one, and if you forget to mention a change, it slips through.

Track Changes marks the edits for you.

TL;DR: Turn on Track Changes from the Review tab for all users, edit as usual, then use Accept > Accept All Changes when the review is done.

Steps

Open the Review tab, click Track Changes, and choose the option that tracks everyone's edits.
Edit the document. In the example, a number was changed. New text appears underlined in red, deleted text is struck through, and a mark appears in the left margin next to the changed line.
When the review is finished, open Accept and choose Accept All Changes. The markup disappears and only the edited text remains.

Checked in the free Word for the web.

Turn it on first

Edits made before tracking is on leave no trace. In the example, a value changed from 30 to 50 before tracking was switched on, and nothing showed that it had changed. If a document is going out for review, switch tracking on before you touch anything.

When to use it

Contracts, proposals and policies where every wording change matters.
Documents passed between several people, where each person's edits should stay visible.
Any time you would otherwise write "here's what I changed" in a message.

Common mistakes

Sending a document with markup still in it. Accept or reject everything before the final version goes out, so the recipient doesn't get the edit history.
Accepting everything without reading. Accept All Changes is fast, but it accepts every change. For a document someone else edited, step through the changes first.
Turning tracking off by accident. If edits stop being marked, check the Track Changes button on the Review tab.

Compared with version control

For developers, this is the closest thing Word has to a diff with a review step: visible changes, then an explicit decision to merge them. The tooling around it is thinner, though. python-docx, for example, has no API for reading or accepting tracked changes, so if your pipeline processes Word files, settle the edits in Word before the document goes back into code.

Drafted with an AI assistant and checked against the original walkthrough in Word for the web.