Add Calculated Field to PivotTable in Microsoft Excel

Microsoft OfficeClients often think that I know more Excel than I do, because I know how to use some functions such as IF, CONCAT and more, but my knowledge of Excel is quite limited when it comes to charts, graphs and PivotTables. I was recently writing a report for a client using Jet Reports to pull Intrastat information from Microsoft Dynamics 365 Business Central (both Jet and Business Central are irrelevant as far as the detail fo this post are concerned).

One of the things I wanted to do was create a summary of the data pulled from Business Central, which meant using a standard Excel PivotTable, something with which I have almost no experience. I managed to get most of the PivotTable configured without issue, but I needed to perform a calculation within the PivotTable and it took me a while to work out.

This is done by adding a calculated field through the PivotTable Analyze tab on the action bar; click the Fields, Items & Sets » Calculated Field button:

Excel showing the Calculated Field button on the PivotTable Analyze tab

Continue reading “Add Calculated Field to PivotTable in Microsoft Excel”

Set New Table Style as Default in Microsoft Word

Microsoft OfficeI’ve been doing some work recently in Microsoft Word, where I’ve been tidying up some of the formatting. One of the issues was the embedded tables were styled to look the same, but they weren’t actually a using style so any new table added, either had to be a copy of an existing one or formatting manually applied.

I formatted the table up as required and applied the format to all of the existing tables and you could apply the style to a new table:

Table with style

Continue reading “Set New Table Style as Default in Microsoft Word”

Change Prompt of a Drop-Down List Content Control in Microsoft Word

Microsoft OfficeWhile I have been using Microsoft Word for years, the most I’ve generally done with it is typing and formatting documents; I’ve rarely used the content controls which, from the Developer toolbar, allow you to add drop-down lists and so on. Well, recently, I was working with a template created by someone else which had a lot of embedded drop-down lists for the client to select answers to questions.

However, the client we were working with was getting a little confused by the prompt for the drop-down lists which said Choose an item (in their defence they were working with inventory items and the drop-down control doesn’t explicitly say to click it. Below is a mocked up example showing what I mean:

Word doc showing drop-down list example

Continue reading “Change Prompt of a Drop-Down List Content Control in Microsoft Word”

Excel Snippets: Formula to Zero Pad to 6 Digits

MicrosoftI 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 posted an Excel formula which could be used to zero pad a column in Excel, which was to be used as the ID column for data being integrated to Microsoft Dynamics 365 Business Central. The formula they had used was longer and more convoluted than was needed.

In fact you only need to use one function to zero pad. That function is TEXT which you provide with two parameters; the cell you want to zero pad and a string of zeros, the length of the output required.

So, to zero pad cell A2 with 6 zeros we would use the following:

=TEXT($A2, "000000")

You can also use the REPT function to provide the required zero [adding length, which if you’re dealing with a long string can ease readability).

The REPT function takes two parameters; the first is the character to repeat and the second is the number of times to repeat:

=TEXT($A2 ,REPT("0", 6))

Stop Microsoft Word Checking for Oxford Comma

Microsoft OfficeOne of the small annoyances I’ve been ignoring in Microsoft Word for a while is the punctuation check it is showing as a problem; this is the Oxford comma (also known as the serial comma, series comma or Harvard comma). When separating a list of values, you do so with commas and the final item in the list is preceded by the word and; the Oxford comma includes a comma before the word “and”.

Microsoft Word in Office 365 has the Oxford comma enabled by default (older versions of Word did not); to disable it, open the Word Options from the File tab, select the b]Profing[/b] tab. Find the Writing Style option, set it to Grammar& Refinements and click the Settings button:

Word Options - Proofing tab

Continue reading “Stop Microsoft Word Checking for Oxford Comma”

Excel Snippets: Get First and Last Dates of UK Tax Year

Microsoft ExcelIt doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

In the previous article of the Excel Snippets series, I showed how to get the first and last dates of the calendar year. One of the peculiarities of the UK, is that our tax year always starts on the 6th of April.

The formula to accurately get the start of the tax year is more involved than getting the sart of a calendar year, but we can break it down to make it easier to understand.

To start with we get the year of the supplied date using the YEAR function:

=YEAR( TODAY() )

Then we check if the supplied date is before the 5th April of that year:

=TODAY() <= DATE( YEAR( TODAY() ), 4, 5 )

The date function is supplied three parameters: year, month and day, which in the above example is the output of the YEAR function, 4 and 5 for the 5th April.

This will return TRUE if it is and FALSE if it isn’t, which will effectively return 1 or 0; in the formula we subtract this from the result of the YEAR function which will give us the correct year:

=YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) )

We can then wrap this in another DATE function which supplies 4 and 6 for the month and day to output the first day of the tax year:

=DATE( YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 6 )

To get the last day of the UK tax year, we just need to copy the above formula, add a +1 to the output of the first YEAR function and change the day parameter in the outer most DATE function to 5[/:

=DATE( YEAR( TODAY() ) + 1 - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 5 )

Updated 13/06/2023: After my error was pointed out, I’ve done some investigating and updated the post above; the formula here now is one posted by Brad Scott in response to a question on a Microsoft Community question.

Excel Snippets: Get First and Last Dates of Calendar Year

Microsoft ExcelIt doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

I recently needed to calculate the first and last dates of a year from a user supplied date for a Jet Report. This can easily be done using a combination of the YEAR and DATE functions.

The YEAR function can be used to extract the year from a supplied date:

=YEAR(TODAY())

The date function is supplied three parameters: year, month and day.

The output of the YEAR function gives us the first parameter and for the month and day of the first day of the year we can simply supply the number 1:

=DATE(YEAR(TODAY()),1,1)

To get the last day of the year, the month parameter is set to 12 and the day to 31:

=DATE(YEAR(TODAY()),12,31)

Excel Snippets: Offset Formula to Calculate Range on Number

MicrosoftI 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:

Example data in Excel

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))

Excel Snippets: Pad Numbers with Leading Zeroes

MicrosoftI 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")