Wizard’s tips by email
Search this site
Topics
- Advice (11)
- Formulae explained (12)
- General (8)
- How to (33)
- Quick tips (18)
- Tools (8)
- Uncategorized (5)
Tags
- ADDRESS
- CLEAN
- CONCATENATE
- Conditional format
- copy
- COUNTIF
- COUNTIFS
- Custom View
- cut
- Data import
- Dollars
- Editing
- EXACT
- Fill
- filter
- Format
- Formulae
- Frequency
- HLOOKUP
- IF
- INDIRECT
- LEFT
- Mail Merge
- MID
- MIN
- MOD
- Navigation
- paste
- Paste Special
- PivotTable
- PivotTables
- RANK
- RIGHT
- ROUNDDOWN
- ROUNDUP
- Series
- Shortcut
- SMALL
- SUM
- SUMIF
- SUMIFS
- Text to columns
- TRIM
- VLOOKUP
- WEEKDAY
Author Archives: Dan
Most useful spreadsheet ever? Excel-based year calendar
I created an Excel year calendar recently. Here's a link to it. It's completely dynamic, allowing you to show the calendar for any year between 1900 and 9999. The only user interaction is to enter the year. The rest of … Continue reading
LEFT and RIGHT default to 1 character
The LEFT formula takes two arguments: the thing you're taking the left-hand side of, and the number of characters you want to take. So the following formula takes the first five characters of cell A1. =LEFT(A1,5) But if you only … Continue reading
MIN
Summary MIN brings back the minimum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values As with SUM, it only applies to cells that are formatted as numeric values … Continue reading
F4: repeat last action
For many things, F4 repeats the last thing you did, whether it's put a border on a cell, change its colour, insert a row, right-align etc. It's a bit hit and miss—it doesn't, for example, repeat data entry. But it's … Continue reading
Editing cells
The easiest way to edit the content of a cell, be it a formula or a value, is to navigate to the cell and hit F2. Instead of moving around the cells in a sheet, the left and right cursor … Continue reading
Conditions don’t care about case
If you're writing a formula that involves comparing two strings of text, beware. Two identical strings of text, one in uppercase, the other in lowercase, will be considered to be the same. Use the EXACT function if you want to … Continue reading
Using a single column for percentages and currency amounts
This morning I received a slightly quirky requirement. The client had a spreadsheet containing people's salaries—a row per person—and wanted to be able to input either a percentage increase or an uplifted salary, and for a separate column to show … Continue reading
Alternate row shading
Alternate row shading is quite useful if you’ve got particularly wide data (lots of columns) and when readers need to scan across individual lines. Bug listings, contact lists etc. I usually use a light yellow shading to help with this, … Continue reading
In-cell carriage returns
Excel allows you to wrap text within a cell or within a merged set of cells. But it also allows you to force line-breaks within cells or merged cells. If you want to create the equivalent of a carriage return … Continue reading