Get Your Free Copy of Our 10 Tips That Will Take Your Excel Modeling From Good to Great Booklet Today!

   10 Lessons in Booklet 

What You Will Learn From Our Excel Modeling Tips

This tutorial is designed for anyone that creates Excel models for budgeting, financial analysis, simulations, inventory management, sales and CRM planning, financial reporting and/or statistical analysis during their day job.  Being Excel mavens our selves, we created this tutorial to help elevate the skills of beginning and intermediate Excel users by illustrating techniques that you can apply in your day-to-day Excel modeling efforts.  

Here’s a summary of the 10 tips in our Excel modeling tutorial:

1. How to Efficiently Manipulate Text Strings With Excel: There are a limitless number of situations where information has been provided to you, the analyst, and now you need to transform it into something useful.  Frequently, being able to manipulate text strings to reformat information can be a valuable skill (this is also a valuable skill in SQL, VBA and other applications).  Here we teach you the basics using Excel’s LEFT(), RIGHT(), FIND(), MID() and LEN() functions, along with some examples of combining multiple functions in one formula.
2. Flexible Worksheet Consolidation: If you have to construct multiple worksheet that have a uniform structure and then consolidate the information on those worksheets, then this is an expert tip for you.  We show you how to set up worksheets that can be consolidated easily on to a summary worksheet.  The flexibility comes in when you are able to add and/or remove worksheets from the consolidation without having to edit a single formula!  This can be very helpful if you generate departmental budgets, employee-level profit and loss statements, etc. 
3. Using Defined Names: User Defined Names can be very helpful if you have to reference the same constant across multiple worksheets and workbooks.  For example, if you are referencing a constant or set of values on another workbook, and unbeknownst to you someone deletes a nearby row or column, your workbook will still be able to reference that value on the related workbook.  Also, you can create arrays of Defined Name Values, which has many very powerful applications.  We provide you with a couple of examples.
4. Loading Data From External Sources: There are many ways to load external data into an Excel worksheet, but one of the most common is loading .TXT and .CSV file types.  In this chapter we go over some helpful tips for loading .TXT data into your workbook application.
5. Leveraging Excel’s VLOOKUP() Function:  The much maligned VLOOKUP() function really does have some useful capabilities.  In this tutorial tip we dive deep into the function and provide some valuable uses it (and the HLOOKUP() function too).  Additionally, we show you how to handle the old NA result and how to actually make some use out of this error result using the ISNA() function.
6. Using Linked Lists in Dialog and Data Validation to Control User Input: In this tip we will illustrate how to use Linked Lists in both Combo Boxes and Conditional Formatting applications to make your report applications more useful for report consumers, all this without having to write custom VBA code.
7. Setting Up Simple User Controls:  Now that we have covered some of the easier stuff, we are going to dip our feet into some basic VBA functionality to again make your report consumer’s lives a little easier.  This will show you how you can easily modify reports on the fly and get them printed easily.
8. Dynamic Reporting Tools:  Another great function for dynamically modifying your reports is using the CHOOSE().  In this basic tip, we show you have to use the CHOOSE() function to update a Gross Margin Analysis Bridge report and describe all the different calculations involved in producing the report.
9. Using Customized Lists to Increase Productivity:  This tip is a very simple way to get some leverage out of an Excel capability that we rarely see users taking advantage of.  Excel does allow you to add in your own custom lists that you can easily paste on a worksheet.  A little difficult to explain, but if you have ever typed “Jan” in a cell and then grabbed the cell handle at the lower right of the cell and Excel automatically started pasting “Feb”, “Mar”, etc. in the adjacent cells and wondered how Excel does that, this is where you show you how to create your own lists.
10. Creating User Defined Function Macros: Whew, that is a lot of tips.  If you still want more, in this 10th and final tutorial tip we discuss how to create and use Defined Function Macros.  Now if you struggled or just skipped over the earlier tips that barely touched on adding VBA code to your workbooks, you really should take a deeper look at what you can do with Defined Function Macros.  Basically, you can use function macros to create your own custom Excel functions.  So if you have some very particular requirements for Excel functionality, this is probably your answer to get the result you need.

Who Will Benefit From Our Tutorial

This Excel Modeling tip tutorial was developed for anyone that is looking to dive a little deeper into Excel functionality to create workbooks that are easier to maintain, more flexible and just provide more insightful information for you and consumers of your workbook applications.  This will include financial analysts, accountants, cost accountants, financial planners and the like.  If you like using Excel and want to learn more, then this is a good place to get some additional tips.   We here at Profitwyse have spent many years mentoring both our subordinates and clients, helping them get more from Excel. 

If you would like to see what other learning resources Profitwyse offers, please visit Profitwyse Academy today and continue developing your skills.