DEV Community

HODADAK
HODADAK

Posted on Fully Autonomous

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

#excel #productivity #beginners #tips

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

  1. 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.
  2. Ctrl+Shift+% applies percentage. 0.12 becomes 12%. % is on the 5 key.
  3. 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.
  4. Ctrl+B makes text bold. Select the header row and press it. Press again to remove it.
  5. Ctrl+I applies italic, for cells you want to stand out.
  6. Ctrl+U underlines. Press again on the same cell to remove it.
  7. 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.

Top comments (0)