Developing efficient management reports

Most management reports are produced using Excel either weekly or monthly. Raw data is imported from other business systems so as to be used in Excel to produce the reports. To make it easier to produce the reports each week or month, the Excel report needs to have a structure that allows easy and quick update of the reports.

Ensure that you designate one or more sheets to be the raw data sheets and have the management reports sheets linked to the raw data using formulas. This will then mean that every week or month, you only need to refresh the raw data sheet(s) and the management report sheet(s) will automatically be updated. The process of producing the management reports then becomes much simpler and faster during the report cycle period.

Data imported from SAP converts dates to text

It is a common problem to find that when you import data from other data sources into Excel, the dates are converted to text. Most times this is data being retrieved for the monthly reporting cycle and there are very strict deadlines to produce the management reports. Most Business Analysts spend their nights in the office to address such mundane problems.

Date problems are best addressed using a combination of Excel Date and Text functions. Sometimes you may need to incorporate a Excel Boolean functions. An example is =IF(ISERROR(DATE(YEAR(D2),MONTH(D2),DAY(D2))),DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)),DATE(YEAR(D2),MONTH(D2),DAY(D2)))

Automate printing of multiple sheets in Excel

I once received this request from a Finance Manager. He had this Excel file with close to 10 worksheets and he only needed to print 6 of the worksheets every month during the management meetings. He needed to print ALL the 6 sheets without having to do it one at a time. This sounded simple, but when I looked at the options for printing in Excel it was not going to be possible.

I therefore developed a macro that would print each of the 6 worksheets in sequence. This I later modified to print a list of selected worksheets based on the users preferences.

Advanced Excel Training

Have you been using Excel for years and do not seem to have completely solved your reporting processes bottlenecks? Do you believe there is a better way to perform some tasks in Excel and yet you have not been able to discover? My expertise in Excel is now easily available in our Advanced Excel Training programs. Excel will never be the same again once you get to attend our training programs. Follow this blog as I give tips on some of the tasks you can do in Excel using more efficient methods.