I like to automate processes as much as possible and creating report headers for updated monthly reports are no exception to this. If you have the same headers every month, but only the Month name changes then here is a great way to automate this.
You can see my report headers in the screen shot below.
Throughout my report I use the current and previous months, in this case Jan 16 and current month which is Feb 16.
I have an input sheet that contains these values and I have named the cells current_month and previous_month which you can see in the Name Box.
So when we enter a new current and previous month value the report header will automatically update. Let’s do this!.
Using CONCATENATE
We use the CONCATENATE function which allows us to join values together. It returns a text string that combines all of it’s arguments. For example, the following formula would return the text string Sales Performance.
=CONCATENATE(“Sales”,” “, “Performance”)
(If you want more information on CONCATENATE then you can read my blog post on it here).
So, let’s build the formula !
=CONCATENATE(“From”,” “,previous_month,” “,”To”,” “,current_month)
And let’s see the result.
Where in the formula
‘From’ is our first argument, followed by a trailing space
‘previous_month’ is the name of our cells that contains the value for our previous month
‘To’ is our fifth argument, followed by a trailing space
‘current_month’ is the name of our cells that contains the value for our current month
Ohh not exactly wht we were expecting is it?. That is because cause the CONCATENATE function, like other Excel functions, operates on what Excel stores internally, not the displayed value.
This results in any date formatting applied to the input cell is disregarded. In order to format the date serial number 42370 as a date, we’ll need to use the the TEXT function. So …let’s go again with more formula fun.
Using TEXT
The TEXT function returns text string with a format that you can specify.
= TEXT(Value, format_text)
Where in the formula
‘Value’ is the cell value you want to format
‘format_text’ which is the type of formatting you want to apply to the cell value surrounded by quotes.
So let’s put it all together…..
=CONCATENATE(“From”,” “,TEXT(previous_month,”mmm-yy”),” “,”To”,” “,TEXT(current_month,”mmm-yy”))
I have formatted the text as mmmm-yy which gives Jan-16 style formatting which is exactly what I wanted.
I can now just change my cell values in the cells current_month and previous_month and my Report Header will always update accordingly. Great Stuff. Give it a try!!.
YouTube Video- Create Your First Pivot Table
Fast Fun Way To Learn Excel Formulas
