Understand an Excel model

[jtreanor]

I have a large, complex group of Excel workbooks each with multiple worksheets and I need to begin understanding them. They were given to me with very limited documentation.

I need some direction interms of how to begin.

 

[Hui]

Most workbooks have 3 functional areas

- Inputs

- Calculations

- Outputs/Reports

Often these are on separate pages or will use different colored cells or borders to distinguish between areas

Start by identifying the inputs

Excel has a number of auditing tools, you can use these to follow the data flow of a cell through a workbook

At every stage try and workout what the author was doing with the data, the data flow will generally follow either a real life physical system or will be replicating what would happen to the data if it were processed manually

Take clues from the sheet names, titles with or above areas and lines around ranges this normally indicates data which gas been grouped for some functional reason

Remember to look for hidden sheets, rows and columns

once you understand the data flow you can start to work through the formula

This may be more difficult if your understanding of Excel functions is limited

Keep an eye out for named ranges, if the have been used well they can assist with understanding due to good naming conventions and vise-versa if they are poorly named

Work on a copy of the workbook and don't be afraid to use color to mark cell ranges as you go

Good luck.