Tag: Excel

Excel: Defining parametric names for cells
It should come as no surprise to anyone who has read my blog for a while that I use Excel on a relatively regular basis. Wherever possible, I like using parameters, rather than addressing something directly. There are a few reasons for this. It makes a formula shorter, more user friendly, and quicker to write.…

Excel: Looking up data continued
Last week, I showed you the simplest form of the XLOOKUP function. Though highly useful, it’s only really useful if you find exact matches. If you’re looking at an array of data – e.g. pay averages for age groups instead of specific ages – you need to help it a little. To this end, the…

Excel: Looking up data
This time, I show you how I look up data in a table using the XLOOKUP function.

Excel: Calculating days, months, and years
One thing I find myself needing to do on a relatively regular basis, is calculate the number of days, months, or years between two given dates. Like with so many other things, Excel has a function for this, too. It’s intuitively named DATEDIF, and here’s how you use it. The syntax is =DATEDIF(startdate,enddate,UNIT). The units…

Excel: Adding multiple axes in a chart
One of the many things I do as a union representative, is negotiate wages with management. I argue my point using a number of tools, not least of which is the change in pay over time. I have one set of data which is what we use when negotiating, which represents the estimated change. I…

Top 15 Excel shortcuts
As I’ve described elsewhere, I use Excel a fair amount. To this end, keyboard shortcuts are very useful. Here are some I find to be very useful: Alt and = Sum up a column Ctrl and + Insert a cell Select a row/column and enter Ctrl and + Insert a row/column above Ctrl and …

Excel: Allow dividing formulas to fail gracefully
I’m sure I’m not alone in having had this happen: I was using Excel to make some quick calculations, and got #DIV/0!errors for some rows because one of the fields I’d been dividing by was empty. Sometimes you don’t have all the information you need for every single row, but you still want to have…

Paste text without formatting
I recently had a long term collaboration with a number of colleagues, where part of what we did was produce, edit, and agree upon finalized versions of text. We applied various formatting such as color, cursive, and bold to track the status of specific text blocks. At the end of it all, we needed to…

Excel: Format cells based on content
As a union steward, one of the many things I do on a regular basis is evaluate key indicators of the company financials. I do this by plotting a few numbers from the accounting reports into Excel, and then run them through some standardised analysis to identify the performance of key indicators. These indicators are…

Excel: Automatically set width and height
If I’ve been there once, I’ve been there a thousand times; I’ve got a spreadsheet in excel with varying widths and heights that don’t fit the data. Now, sure, you could go in and correct it manually – but that’s not only boring, it’s a pain it the behind. Luckily, there are keyboard shortcuts to…

Excel: Merge cells without losing data
If you’ve ever attempted to merge cells that hold data in Excel, you will almost certainly have encountered this warning: This can be annoying when you’re trying to combine data in multiple different fields, such as first and last names. Happily, there is a formula (in Excel, there’s more or less always a formula) to…