Category: Office

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

This is based on an example a colleague discussed with me for dynamically calculating the sum of a range of columns based on the user specificed number of months.

The data for the months of the year was going across the worksheet with a total at the end, but he only wanted to total up the rows up to the required month. The example below shows the required output, with the user specifying month 7 (July) in cell B1 and the required total value in cell O3 which is the sum of C3:N3:

If we’d wanted the whole row, this would have been quite easy to do using the SUM formula:

=SUM(C3:N3)

The problem is though, that the second cell in the range needed to be variable. Fortunately, Excel has another function which we can use; that function is OFFSET:

=OFFSET(cell reference, rows, columns))

In our example, we needed to set the cell reference parameter to B3, the rows to 0 and the columns to B1:

=OFFSET(B3,0,B1))

This OFFSET will, when combined with the SUM formula, give us the result we need of summing from B3 through the columns to I3 when month 7 (July) is speficied by the user in cell B1:

=SUM(C3:OFFSET(B3,0,B1))

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

A colleague was recently working with a client who was preparing data for import. One of the issues they encountered when cleansing the data was that Excel stripped the leading zeros from the ID fields and were looking for a way to add them back in order to import the file with the required ID format.

They were looking for a formula to do this and came up with an approach of prepending 0s and using the RIGHT function, but there is a claaner approach using the TEXT function.

The TEXT function is very flexible and one I’ve used before when formatting numbers to two decimal places.

The ID numbers were a variety of lengths, but needed to be formatted to six characters long with zero prefixes.

This can be simply done using the TEXT function as shown here:

=TEXT($A52,"000000")

I had a strange occurrence recently with Microsoft Excel where the column headers changed from letters to numbers. I’d didn’t notice initially, but it was when I was working with a formula that I realised it wasn’t putting letters in the cells but an odd numbering style.

When I looked into why I discovered the column headers were numbers:

Continue reading “Microsoft Excel Column Headers Showing as Numbers Instead of Letters”

When working with a large document such as a book, a common requirement is to add an index to the end so that important words or phrases can be indexed for easy lookup. While you can tag words for the index within Microsoft Word a better way to do in bulk is to use a concordance file.

This is basically a separate two column file containing a list of words to index in the first column and how they should be shown in the second:

Continue reading “Applying a Word Concordance File to Create an Index”

I hated threaded email when it first came out, but I’m now used to it and as it turns out prefer it to non-threaded. I discovered this after changing jobs; my old work laptop had threaded emails enabled and the one at the new job didn’t.

I worked that way for a few weeks and it was fine as I was doing a lot of learning and not involved in many projects. As soon as that changed and I started getting lots of emails I wanted to enable threaded emails, but had some trouble finding the option.

I eventually found it and there were two reasons why I was having problems finding the setting; the first was that I was looking in the Options and the second was that I was looking for something which included the word “thread”.

It turns out the setting in the Outlook desktop client is on the View tab and is called Show as Conversations:

Continue reading “Enable Email Conversations in the Outlook Desktop Client”

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I’ve been using this formula for quite a while now, but have to admit that I did not create it and no longer remember from where I got it.

The basic use is that you would have the starting amount in cell C2 and then use this formula in the next cell down and replicate down the page and it will calculate interest on a monthly basis.

The first highlighted section is the % rate being charged on the mortgage and the second is the amount you’re paying each month.

=IF(C3<=0,0,ROUND((((1+({% rate}/100)/12)^(12/12))-1)*C3,2)+C3-({mortgage payment amount}))

I’ve used this formula for a while to calculate an estimate of my mortgage it calculates to within a reasonably close amount (usually within a handful of pounds) as my mortgage interest is calculated daily, not monthly, but I’ve not been able to work out/find a formula for daily interest.

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I was have a tendency to track personal financial data in Excel and was recently updating my electricity usage sheet and wanted to set a date column to auto incremement by one month for each row. I did some checking and found that the EDATE function can be used to get the same date of the previous or future months based on the second parameter.

The structure opf the function calls is shown below:

=EDATE(start date, number of months)

For example, if I wanted to add one month to todays date, I would use the following:

=EDATE(Now(), 1)

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

Most, if not all, of the programming/scripting languages I work with will have a function allowing you to calculate a difference between two dates, but I always have to look them up.

It’s not too difficult in Excel; this formula can be used to work out the number of days difference between the values in cells A2 (start date) and A3 (end date):

=DATEDIF(A2,A3,"D")

The highlighted section can be several values depending on how you want the date difference calculated:

Unit	Returns
D	The number of days between the supplied dates.
M	The number of months between the supplied dates.
Y	The number of years between the supplied dates.
MD	The number of days between the supplied dates ignoring the months and years.
YM	The number of months between the supplied dates with the days and years ignored.
YD	The number of days between the supplied dates with the years ignored.

More details and examples on these units are available from the Microsoft Support article which notes they don’t recommend MD, but, to be honest, I don’t see much use for any of the last three listed above.

Bookmarks are not shown in Microsoft Word by default; this minimises the risk of someone accidentally deleting or changing a bookmark. However, sometimes you do need to make them visible.

I had to do this recently, but really had difficulty finding the setting.

To enable the display of bookmarks, click on File and select Options.

In the Word Options window, select Advanced and scroll down to Show document content; the fifth setting down is Show bookmarks. Mark this setting and click OK:

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I tend to do data manipulation in SQL Server rather than Excel, as it is generally easier to do it in SQL where I can run commands sequentially. However, there are times when I do need to do it in Excel.

I recently needed to replace one part of a string of text and really had trouble doing so. This was because I am used to using REPLACE in SQL and str_replace in PHP and expected the REPLACE function in Excel to allow similar replacement. However, I rapidly found that this was not the case.

I did some exploring and found that the command in Excel is actually SUBSTITUTE:

=SUBSTITUTE(A2,"Apples","Oranges")

This will replace the word apples with oranges in the string in cell A2.