Excel Tips & Tricks Top 10 tips and tricks to become more efficient and save time Leila Gharani, MA Consultant & Trainer Hyperion Financial Management Microsoft Excel Excel Tips & Tricks 1 Table of Contents TIP #1: BE AWARE OF THE NEW FEATURES ......................................... 2 TIP #2: MASTER THE SIMPLER TASKS.................................................. 4 TIP #3: GO BEYOND THE VLOOKUP .................................................... 6 TIP #4: LEARN NEW FUNCTIONS ........................................................ 8 TIP #5: SPEED UP EXCEL ................................................................... 10 TIP #6: USE CONDITIONAL FORMATTING EFFECTIVELY ..................... 12 TIP #7: EXPAND YOUR HORIZONS .................................................... 13 TIP #8: CREATE A DASHBOARD ........................................................ 14 TIP #9: KNOW THE POSSIBILITIES ..................................................... 16 TIP #10: IMPLEMENT A WORKSHOP ................................................. 18 EXCEL TRAINING & CONSULTING ..................................................... 19 ABOUT LEILA GHARANI ................................................................... 20 Note: Footnotes contain equivalent names of German functions Excel Tips & Tricks 2 Tip #1: Be Aware of the New Features When Microsoft introduced Excel 2007 they introduced new features, improved functionalities and interface, but many people still use it the way the used the old Excel. There is much benefit that can be gained from these new features. They can help you do your analysis faster by using one of the new formulas or functions introduced. They can help you create a cleaner and easier to understand file. All in all, they can help you become more efficient in your work and as a result save time down the road. More obvious changes If youre currently working with Excel 2007 or above, youve probably noted the more obvious changes. These are: New user interface New file format which creates smaller files as compared with older versions More rows and columns to build more capable worksheets: Excel supports over 1 m. rows and 16 k. columns Memory management increased Improvements in presentation, reporting and the look of charts New themes and styles to harmonize reporting books Easy formula writing using formula auto complete Better printing experience and ability to save files as PDF Excel Tips & Tricks 3 Less obvious changes Some additional features might have gone unnoticed. Below is a glimpse of the main updates: New functions: I am quite certain anyone using Excel for any type of data analysis would highly benefit from these new formulas. These are SUMIFS, COUNTIFS & AVERAGEIFS1 which allow calculations matching multiple criteria (unlike the old SUMIF without the S) In addition the IFERROR2 formula is much easier to write than the old If(ISERROR(),,,) formula. Rich conditional formatting: Excel users employ conditional formatting to make their data stand out for presentations, dashboards and analysis. The prior versions of Excel were restricted to a maximum of three conditions. From Excel 2007 and above, you can use as many rules as required. You can also apply several rules to a single cell and stop evaluating rules after a condition has applied. New data formats are also available such as data bars, colour gradients and icon sets. Turn your data range into a table (CTRL + T): This feature was highly improved upon in Excel 2007. To name a few advantages: it lets you automatically update your charts or validation lists once you add new data (no need to manually adjust or use OFFSET formulas). It also lets you use structured referencing, meaning your formulas update automatically once you add new data to your rows. And more improvements: Additional enhancements in terms of sorting and filtering, shared charting, simpler pivot tables and easier connections to external data have also been introduced. 1 SUMMEWENNS, ZHLENWENNS, MITTELWERTWENNS 2 WENNFEHLER more efficient than ISTFEHLER Excel Tips & Tricks 4 Tip #2: Master the Simpler Tasks There are a handful of the more commonly used tools that can help working with Excel easier. Some might already be obvious to you and some new. One main advantage of the newer Excel versions is that some very useful features became easier to access. They are now just a mouse click away. Previously you had to go through one menu and then sometimes another, to be able to find the feature you needed. Customize Excel In Excel 2007 & above, you can customize the Quick Access Toolbar to include the buttons and commands you use on a frequent basis. This toolbar acts as your favourites and allows you to group together your most used Excel buttons. This saves time as you dont need to switch between the different ribbon tabs to locate the buttons you need. From Excel 2010 onwards you can also customize the Ribbon by adding your own tab and including in that tab all the functions and features you commonly use. Use Groups to hide without hiding Groups are available in the Data tab in the Outline Section. They allow you to group rows and columns together so that they can be hidden from view. It is suggested to generally group cells instead of using the hide cells feature. The advantages are: Visibility: The + and signs signal whether there are any hidden rows or columns. Excel Tips & Tricks 5 Multiple levels: With groupings, you can have multiple levels of hierarchies which is not possible if you were hiding rows or columns. The nested groups allow the users to have subtotals within groups and then drill-downs to a more detailed level. Excel currently supports up to 8 nested groups. Navigating large workbooks When you work with workbooks that contain many tabs you will find yourself spending a lot of time scrolling left and right to see the one you need. It makes it easier to right-mouse click on the little scroll bar arrows at the bottom left-hand corner of your workbook. The right-mouse click brings up the list of all the tabs in the workbook allowing you to quickly jump to your selected tab. And many more Other more commonly used tools that can make working with Excel easier are features such as removing of duplicates, introducing validations, freezing panes, working simultaneously in different windows or tabs, inspecting workbooks, protecting them, creating templates and utilizing the potential of custom number formatting. There is always some useful feature in hiding. Excel Tips & Tricks 6 Tip #3: Go Beyond the VLOOKUP The VLOOKUP3 function is one of the most used more complex Excel formulas, because it can search a list or a table in the left-most column and return the respective value from one of the adjacent columns. HLOOKUP works exactly in the same way except that it works horizontally rather than vertically. Advanced users generally avoid VLOOKUP because of its limitations VLOOKUP cannot go LEFT: The value that you need to look up must always be on the first column on the left and the values to return must be on the right hand side Two way look up not possible: You cannot VLOOKUP on the columns and the rows to find an exact match The INDEX and MATCH4 formula is superior to the VLOOKUP on many levels It can look to the left or the right. Unlike VLOOKUP which can only go right, INDEX and MATCH can look in any direction You can do a two-way lookup by looking along the row and along the column in a matrix to find the intersection At its worst it is slightly faster than VLOOKUP and at its best it is SIGNIFICANTLY faster than VLOOKUP Note that INDEX and MATCH is not one function but two functions written in one formula. Each function alone is not very useful but together they are very powerful. The INDEX part returns a value based on the specification of how many rows to go down and how many columns to go right. The MATCH part 3 SVERWEIS = VLOOKUP, WVERWEIS = HLOOKUP 4 INDEX und VERGLEICH Excel Tips & Tricks 7 finds the look up values row index or column index and passes this along to the INDEX function. Sounds complicated? Its actually quite easy in practice. All in all if you master the INDEX and MATCH formula be sure that you will simplify your data analysis and reporting. There are many varieties and uses of this formula. Below is an example of a more complex matrix lookup where you need to dynamically move down rows, and also across the columns. This requires a double match. Excel Tips & Tricks 8 Tip #4: Learn New Functions Using the right functions for the task at hand saves time and headaches down the road. There are many useful functions in Excel that are extremely practical when doing data analysis. Its not so important to know all the formulas by heart. Its enough that you are aware of the possibilities and that a certain task could be solved faster, better, or more efficiently using a given formula or feature. Below is a list of some very useful functions for data analysis and manipulation: SUMIFS, COUNTIFS and AVERAGEIFS functions. These were introduced with Excel 2007. The addition of the S allows for more criteria to be checked. The traditional SUMIF and COUNTIF functions allowed only 1 condition whereas the new functions allow up to 127 criteria. For example you can sum the values in Column C if the values in Column A and Column B meet a certain criteria you can set up to 127 criteria. Text formulas such as LEN, LEFT, RIGHT, SEARCH, UPPER, LOWER and PROPER5 are quite useful when you need to manipulate descriptions, names etc. Date functions such as WEEKDAY, WORKDAY and NETWORKDAYS6 can be really helpful when planning your project deadlines or reporting timetable. MAX, MIN, LARGE and SMALL7 functions help find the largest, smallest or the nth largest or smallest value. These are practical 5 LNGE, LINKS, RECHTS, SUCHEN, GROSS, KLEIN, GROSS2 6 WOCHENTAG, ARBEITSTAG, NETTOARBEITSTAGE Excel Tips & Tricks 9 functions for ranking. For example finding the product that generated the 3rd highest sales, or the company that had the 5th highest profit. OFFSET8 is a very useful function especially when designing dashboards and flexible reports, as it returns the value or values in a range by moving from a given starting point. RANK9 function returns the rank of the number in relation to a list of numbers. You can use the RANK function together with INDEX and MATCH or the OFFSET function to dynamically rank companies based on their results or products according to their sales value. SUMPRODUCT10 function is a formula that can work on arrays. The most common use of it is to get the sum of products in one formula by skipping intermediary steps. However this function is much more powerful than that because it can be used in non-obvious ways. You can actually use this function to do what INDEX and MATCH can do, what the SUMIFS functions can do and even what the COUNT function can achieve. 7 MAX, MIN, KGRSSTE, KKLEINSTE 8 BEREICH.VERSCHIEBEN 9 RANG 10 SUMMENPRODUKT Excel Tips & Tricks 10 Tip #5: Speed up Excel Excel 2007 & above allows you to manage and create bigger and more complex spreadsheets than what was possible before. It also came with improvements in terms of speed. It does not however, stop a user from creating poorly designed spreadsheets by not utilizing the most efficient function for their calculations which in turn could result in slow-calculating files and thus frustrated users. By utilizing some straightforward techniques and understanding how Excel works with functions, you can significantly improve any slow-calculating workbook. The design of your Excel spreadsheet is key in defining how efficient and fast your workbook runs calculations. What makes spreadsheets slow is not the number of formulas or the size of your workbook but the number of cell references in your formulas and the efficiency of the functions used. It is also important to understand how Excel runs calculations, which cells are calculated and the difference between volatile and non-volatile functions. What are volatile and non-volatile functions? Volatile functions are formulas that automatically get recalculated any time you enter data in any open workbook. They get calculated independent of whether your formula is referencing these cells. It could be anywhere in any cell that has nothing to do with this formula. They also get calculated anytime you insert a new row or column or you rename a worksheet or change the position of worksheets. How do normal formulas work? Normal non-volatile formulas get recalculated any time you change a cell that impacts the formula. If you change a cell that has no impact on Excel Tips & Tricks 11 the formula nothing gets recalculated. Lets assume you have a large Excel file that has thousands of formulas and many of these formulas reference other cells which might in themselves also be formulas referencing other ranges. This dependency chain is memorized and stored by Excel in a dependency tree. Excel uses this dependency tree to decide which cells to recalculate and which cells to ignore. For normal formula calculation Excel always refers to this dependency chain. If you make one change to a cell, it updates its dependency tree. Impact of volatile functions Note that even though volatile functions get recalculated every time you make a move in Excel, the use of these functions does not necessarily slow down your workbook. It just might slow it down depending on the size and complexity of the workbook. If you find that your workbook is very slow and you are using these functions then it would be best to look for alternative methods for calculation. Sometimes you can replace volatile functions with non-volatile ones such as instead of OFFSET, use INDEX and MATCH function. VBA (Visual Basic for Applications) is also an option for complicated workbooks and calculations. Improving your spreadsheet The major contributors to slow files are: Invalid links to external data & workbooks Invalid cell references and names Extensive usage of volatile functions and conditional formatting Referencing too many unneeded cells, such (D:D) instead of (D1:D200) By keeping track of the above points you are well on your way to creating highly efficient spreadsheets. Excel Tips & Tricks 12 Tip #6: Use Conditional Formatting Effectively Conditional formatting is a very useful tool that helps make your data stand out. That way information is easier to read and digest. It is extensively used in dashboards and reports. You use conditional formatting to emphasize parts of your reports by applying specific formatting if a certain condition is met. Prior to Excel 2007 a maximum of three conditions were supported. Post 2007 Excel can have as many conditions as is needed. You can use in cell bars and icons to bring attention to parts of your data or highlight a row based on a certain value. One use of conditional formatting that might have gone unnoticed is its ability to compare two lists and highlight the duplicate or unique vales. No need to use functions to compare, just a few simple mouse clicks is enough. Bars achieved using Conditional Formatting in Excel 2010 Excel Tips & Tricks 13 Tip #7: Expand your Horizons Awareness of functions and features of Excel is crucial in creating valuable reports and analysis. If you dont know what possibilities are available, you will create inefficient reports or incorrect analysis. Some very useful features are: Pivot tables offer quick ways of doing analysis. They can do the same thing as SUMIF, COUNTIF and SUMPRODUCT functions except that they are significantly faster. In addition, the Pivot Slicer is available from 2010 which allows for better filtering of the data, giving your pivot a dashboard look. From Excel 2010 onwards Microsoft has introduced a free add-in called Power Pivot which turns Excel into a Business Intelligence tool. It can be used by general Excel users and does not require any specific IT knowledge. Goal Seek and Solver help you solve issues for cases when you know the end result but are not sure what the input should be. With Solver you can introduce many constraints as well. For example you receive a total budget for 10 projects and you want to distribute it between the projects respecting certain constraints. Solver can help you do that with a few mouse clicks. With Name Manager you can create formulas that are easier to understand and follow. You can also create formulas that can be used to automatically update charts once new data is added. Formula auditing features such as the watch window and the Evaluate Formula tool allow for quick debugging of lengthy formulas that return errors. Excel Tips & Tricks 14 Tip #8: Create a Dashboard Companies that need more complicated systems to handle their databases and consolidation, still rely heavily on Excel as a data reporting and analysis tool. Excel is also a great tool for dynamic and interactive dashboard solutions. Advantages of dashboards The main benefits of dashboards are Visualization: Effective visualization of data is not only informative but appealing, and as a result more engaging Highlight problems: Due to the use of visual media you can highlight trends and changes in KPIs to quickly visualize problem areas Avoid complexity: Dashboards allow you to look at different reports in one place, simplifying data analysis and investigation Dashboard Example Tabular reports can make you slow. They consume more energy as you try to find trends and interpret data. Excel Tips & Tricks 15 Above is a KPI dashboard created in Excel from retrieving data from Oracles Hyperion system. It enables scrolling to ensure that all companies are viewable in the same window. It also enables sorting so that all the companies are sorted based on the KPI chosen. Graphs are dynamic and animated. The user can for example, tick mark budget and the comparison is provided. All this is done using PURE Excel formulas. No macros or VBA is needed to create valuable dashboards. The advantage of such dashboards is that you can connect the data to any system you have so that all you have do when you open your Excel dashboard is select your point of view for month, year etc. and click refresh. The latest information is instantly available. Excel Tips & Tricks 16 Tip #9: Know the Possibilities The problem with Excel is that because its so easy to use its generally misused. Anyone can create Excel spreadsheets - badly. To create a spreadsheet you dont need special knowledge or be advanced. The Excel product itself is aimed to work for any type of user independent of level of knowledge. What can end up happening though is a set of spaghetti spreadsheets, all somehow intertwined into each other. No one really understands how they work anymore but somehow they still work so they continue to use them and pass them on to the next colleague. Until something goes wrong or something crashes. By that time no one really understands how to fix the problems anymore. Sometimes the easiest way of dealing with these spaghetti applications is to build them from scratch, instead of trying to rework the existing ones. Use VBA/Macros to automate You can use Visual Basic for Applications (VBA or a macro) to create stand-alone programs and tools that enable you do certain tasks. With VBA you can: perform complex analysis control other applications from Excel create tools that map data from one database to another create more sophisticated dashboards automate any repetitive task The options are endless. As long as there is a certain logic to a task, it can be automated. Excel Tips & Tricks 17 Create valuable templates Excel can be used creatively and smartly to design templates for different purposes. The below is an example of an Excel Template that can be used for project management. It is fully dynamic and uses formulas, charts and conditional formatting to quickly illustrate how much of a project has been completed, where the current week is and how much was originally planned. Excel Tips & Tricks 18 Tip #10: Implement a Workshop Reading about new features and functions or attending a seminar that teaches them is easy. Implementing them in your daily tasks is the real challenge. Issue #1: Time The problem that most people face after attending a technical training is to actually implement what they learnt in their everyday life. During the training you might come up with great ideas on how you can improve your reports, interfaces, create interactive dashboards but once you get back to your desk you get tied down with your own tasks that you never find the time to actually implement your new ideas. Issue #2: How Sometimes youre not sure where to start and would like to get some direction on how best to design your file or tackle the task at hand. A hands-on workshop will take you step-by-step through the process and get you started. In order to support the effectiveness of what you learn and to make sure the new concepts are also applied in real work life, its recommended to conduct a workshop which allows you to 1. Dedicate the time needed for the task and 2. To get you started in the right direction This ensures that you not just learn a new method but actually utilize it. Excel Tips & Tricks 19 Excel Training & Consulting Once companies are actually aware of what Excel can do in terms of processing and automating data and reports, it will change how they work. The Advanced Excel Training session introduces you and your team to new functions and methods that will help you simplify your work. Advanced Excel training agenda Changes that came with Excel 2007 & above Top 10 tips that can save you time Top 10 Excel functions every Excel user should know Go beyond VLOOKUP to learn over 10 valuable functions The benefits of Excel Tables Conditional formatting Name Manager and why its useful Charting tips & tricks Pivot tables or Goal Seek & Solver For more information and other available training packages such as the Excel Basic or the Dashboard training packages please visit www.xelplus.com Consulting Youd like to improve your reporting processes but are not sure where to start? Or youd like to introduce automations and checks or map data from one database to another and are unclear how to approach the project. Visit the website above for more information. http://www.xelplus.com/Excel Tips & Tricks 20 About Leila Gharani Leila Gharanis goal is to empower users of Excel and Consolidation systems with the knowledge they need to work effectively. Leila supports companies in solving specific issues in data analysis, reporting and consolidation. She has over 15 years of experience implementing and training users on Management Information Systems of different sizes and nature. The training sessions are all customized and tailored to the clients system and audience. All sessions are hands-on. Especially with technical topics, users learn the most when they attempt to solve a problem on their own. They are most engaged when they make mistakes and attempt to fix these. Main areas of focus are Hyperion Financial Management (HFM): advice, implementation & support HFM Training: fundamentals, advanced and key user Excel Information Systems: Automation via VBA, dashboards & Reports Excel Training: Basic, Advanced, Dashboards Education background Masters degree in Economics, University of Toronto, Canada Bachelor of Social Science degree, Carleton University, Canada Microsoft Certified Excel Expert / Office Specialist Master To find out more please visit www.xelplus.com http://www.xelplus.com/Excel Tips & Tricks 21 Leila has a very deep knowledge of Hyperion Financial Management, both from the technical and user perspective. As project manager, she was responsible for the successful implementation of our HFM applications used for consolidation and reporting purposes of Mondi Group. She is very results oriented, has the ability to simplify complex subjects, is very analytical, and has a structured working style. Leila has trained our users with outstanding feedback. Her training sessions are fully customized based on our needs and requirements. I highly recommend her. Walter Seyser, Controlling Director Europe & International, Mondi AG The HFM user training showed me how to put together my own Smartview reports and helped me understand some of the basic and even not so basic functionalities. The content covered the functionalities that were most practical and helpful for day to day work, and everything was taught in an easy to understand way. I found the experience outstanding, much needed and way overdue. Leila is an excellent instructor. One of the best Ive ever had. Ron Saia, Finance Director, Tekkote, New Jersey, USA One thing I liked about Leilas Advanced Excel training was the practical and systematic approach. The training included lots of examples and exercises, many of which were based on our files. Leilas great knowledge and enthusiasm is a highly motivating factor for anyone getting trained by her. She can explain complicated issues in a way that they seem easy and understandable. I have learnt new ways to improve my Excel skills which I need for my everyday work. I am encouraged to learn more and improve further. I definitely recommend the training. Maja Mahler-Hutter, Europe & International Controlling, Mondi AG, Austria