When working with a report there might be times you’d like to see the information displayed in a different format. A common question people ask is, how they can see month names instead of period numbers in their column headings. With Microsoft® Excel® this is easy to do.
In this tip we’ll focus on the Sage Intelligence Cash Flow report and using the Excel VLOOKUP function to add the month names to your column headings.
If you would like more information on the VLOOKUP function, take a look here.
The Cash Flow report is a free report provided by Sage Intelligence which you can download straight to your Report Manager using the Report Utility. If you’re not already using the Utility or would like more information on it then take a look here.
Once you have run out the Cash Flow report from the Report Manager, follow these steps to add your months:
1. Insert a row above the period numbers row.
2. Scroll to the bottom of the report and then list the numbers 1 to 12 in the first column. Ensure the values are in a number format – if you get a green corner in a cell, click on the exclamation mark and select Convert to Number.
3. Then enter the month names against their corresponding periods.
4. Now you need to insert the VLOOKUP function. Select the cell above the first period at the top of the worksheet and enter:
=VLOOKUP(“Period Cell”,“Period & Month table”,”Column number”,FALSE).
In my spreadsheet, my VLOOKUP looks like this:
=VLOOKUP(E$11,$B$56:$C$68,2,FALSE).
You will notice I have used the F4 key on my keyboard to absolute reference the row for my Period Cell. I have also absolute referenced my table as this will not change.
5. The last thing to do is drag the formula along the row to have the months populate above each period.
Two other things to remember are:
- If you don’t want to see the period numbers you could hide this row. But don’t delete it as the formulas we have just entered depend on it.
- Remember to save the workbook back to your report in the Report Manager.
As a last note, if you’re interested in customizing your Cash Flow report further, check out this recorded webcast: How to fully customize the Sage Intelligence cash flow report. In it you’ll learn how to convert the report to show the current period cash flow only.
