There is a vast array of Excel books in the market screaming for our attention, but have you ever come across one that effectively teaches the application of Excel through case studies? How often does one find himself or herself still terribly inadequate and incapable of applying their Excel knowledge into their office Excel tasks even after reading a thick book of Excel instructions?
Leveraging on a number of valuable case studies within the real business context to reveal the secrets of data analysis in Excel, this book clearly separates itself from the run-of-the-mill Excel Books. In each case study, the author demonstrates step by step how certain functions and formulas can be cleverly combined to overcome many data analysis challenges in the real business environment. In a league of its own, Hidden Secrets of Data Analysis in Excel is a must-read guide for all those who are crying out for productivity breakthroughs in managing their data with Excel.
The Excel book is organised into seven realistic case studies.
In the first case, you will learn how to work with dates, which will always be present in any data records. It could come in the form of employment date for HR, invoice date for finance and accounting, date sold for sales, shipping date for logistics, etc. Unfortunately, most people neglected the importance of entering the right form of dates in Excel. As a result, they are not able to make use of the date functions that are readily available in Excel. In the book, the author shares about how dates and time are stored in Excel and how you can avoid wasting your time trying to clean up your data for analysis. You will also learn this secret formula that can calculate number of years accurately to the nearest day. Using the same case study, the author will also clear the misconception that most people have about the subtotal formula and explain how to maximize its use. Continuing with the same case, you will learn how to classify data into groups quickly and let Excel automatically highlight key information without you having to eyeball at the data row by row.
While some Excel users are able to select cells in Excel by manually clicking the mouse at the required cell or range of cells, others are aware that by adding the control key, one is capable of selecting different parts of the spreadsheet. But do you know that Excel is not limited by just these? In this book, you will discover that things can get even much simpler. In the second case, the author will show you how you can select the cells that you need in a few clicks, no matter how huge the spreadsheet is. With this little secret, you will be able to select all the blanks in a spreadsheet in just a few seconds. Yes, in seconds and not even minutes. This is one function that will tremendously boost your speed working with Excel.
Those who work enough with Excel will totally agree that learning more formulas in Excel does not make one a more advanced and more efficient user of Excel in their work. The secret is in learning how to combine formulas together to achieve the desire effects. Just like in cooking, you need to add different ingredients for a dish, in Excel, you need to combine formulas and functions together to solve a practical Excel problem. In the third case study of this Excel book, you will learn how to combine formulas together to solve a practical problem. Learning the right concept from the beginning will start you on a more fruitful journey working with Excel. It will make working with Excel a lot more meaningful and interesting for you.
Pivot Table is one of the best analytical tools in the market. Using pivot table, thousands of rows of data are summarized and converted into meaning reports instantly. The fourth case study will show you how to set up a pivot table, instantly identify your top customers, top products, and top performers in seconds. Armed with the correct knowledge of pivot table will produce an unprecedented amount of time saving in your analysis.
MS Query is a relatively unknown yet amazing function. With this function, you can tap into databases and prepare real time reports without depending on expensive systems. Combined with Pivot Table, the fifth case study shows you how you can make your daily, weekly and monthly reports update automatically. This is an extremely useful function especially when you need to retrieve data from your colleagues and the data are presented in columns that are sparsely spread out.
Excel users tend to shun macros when they hear that it is related to programming. In this case study, you will learn first hand how you can create your own macro and activate your self created macro with just one click. Complete this skill with the knowledge of removing the macros from the program so that it does not get overloaded with too many macros.
The Excel book ends with this final case study on how to consolidate data easily from multiple files and get updates as and when needed. This is a powerful solution if you have to consolidate data from all your team members to see the overall results of your team. Through this case study, you will also know how MS Query can solve your problem of copying data from columns ABC and pasting them into different column sequence such as columns BCA.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft