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
- ACOS
- ADDRESS
- Chart
- COLUMN
- CONCATENATE
- Conditional format
- COS
- COUNTA
- COUNTIF
- Custom View
- Dates
- Dropdown
- Format
- Formulae
- Frequency
- IF
- INDIRECT
- LARGE
- MAX
- MID
- MOD
- Named ranges
- Navigation
- OFFSET
- Paste Special
- Percentages
- PivotTable
- PivotTables
- PRODUCT
- RADIANS
- RAND
- RANK
- Reference
- Ribbons
- ROUND
- ROUNDDOWN
- Shortcut
- Sorting
- Sudoku
- SUM
- SUMIF
- Syntax
- Text to columns
- Trigonometry
- VLOOKUP
Tag Archives: VLOOKUP
VLOOKUP and HLOOKUP
VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data. But we shouldn't forget about its lesser-used sibling, HLOOKUP. First, VLOOKUP. … Continue reading
Number to text converter: most useless spreadsheet ever
I can’t remember why, but I set myself a personal challenge some time back to create a spreadsheet that could convert numbers to text. Here it is. Type in a number between 1 and 999,999 in the red cell and … Continue reading
Variable vlookup offsets
Challenge: I want to future-proof the column number returned in my VLOOKUP formula. VLOOKUP is a hugely useful formula in Excel. Apart from the basic mathematical operators and the IF statement, there’s nothing that touches it in terms of how … Continue reading
Hiding errors
Challenge: I want to hide error values. Error values are pretty ugly. #N/A, #VALUE, #REF etc. When the eye sees them, it triggers an ever so slight wince. They occur when something's awry: when lookups don't yield any results, when … Continue reading
Breaking rank—differentiating between values that are equal
Challenge: On a dashboard report, show the top five values from a table of data showing the number of respondents. And against these values, provide other data from the same records. At first glance, the above challenge seems rather simple. … Continue reading
Killer Sudoku Pro: spreadsheet helper
Challenge: There is a variant of Sudoku called Killer Sudoku in which areas of the grid, either square, rectangular or some other irregular shape, are caged off. A small number in the corner indicates the value to which the numbers … Continue reading