{"id":1051,"date":"2014-10-29T18:24:17","date_gmt":"2014-10-29T23:24:17","guid":{"rendered":"http:\/\/flevy.com\/blog\/?p=1051"},"modified":"2014-10-29T18:29:28","modified_gmt":"2014-10-29T23:29:28","slug":"4-excel-functions-every-business-professional-needs-to-know","status":"publish","type":"post","link":"https:\/\/flevy.com\/blog\/4-excel-functions-every-business-professional-needs-to-know\/","title":{"rendered":"4 Excel Functions Every Business Professional Needs to Know"},"content":{"rendered":"

\"Close-up<\/a> Microsoft Excel is powerful. You can use Excel to solve simultaneous differential equations, develop financial statements and run a statistical analysis on large data sets. The power of the program comes down to the user\u2019s creativity and the software\u2019s copious functions and formulas. With the introduction of Excel on the new\u00a0Apple iPad Air<\/a>, you can have this power with you anywhere you go.<\/p>\n

Record Macro<\/h2>\n

Doing the same functions over and over is a waste of time and energy. Excel\u2019s macro function is a visual basic application (VBA) that allows you to record your tasks and use them, via shortkey, whenever you wish. To use this function, click record macro under the developer tab. Then, a dialogue box will ask for a name and save location for the macro. Once you click OK, Excel will begin recording your selections. Be careful at this point because Excel records everything, including your mistakes. When you are done with your set of tasks, click stop recording. If you have an eye for code, you can open the visual basic editor, and tweak the code as you see fit. The syntax is pretty simple, and there are\u00a0explanatory lists readily available online<\/a>.<\/p>\n

VLOOKUP and INDEX-MATCH<\/h2>\n

If you are working with a small workbook, then it is easy to scan your data. However, when you have a 50 by 2,000 table running, finding specific numbers and organizing data can be a nightmare. The VLOOKUP function tells Excel to find a specific data set and return the value. You also can set up Boolean expressions like greater than and between. Since VLOOKUP has some limitations, using the\u00a0INDEX and MATCH may be a better way to find data<\/a>. INDEX lets you set a data array area, and then MATCH pulls matching data from that set. This allows you to group datasets and gives you more control over the search criteria. <\/p>\n

Flevy has an extensive library of Excel documents<\/strong><\/a>, ranging from Valuation Models to Marketing Tools. \u00a0Peruse\u00a0them all here.<\/a><\/div>\n

Formula Auditing<\/h2>\n

Large workbooks with a lot of formulas can quickly become a nightmare when you need to check the locations of all of the supporting data. This is why Excel has an auditing section under the formula tab. You can click on the trace precedent button to turn on arrows that\u00a0show you what cells are used to calculate a selected value<\/a>. Going in the other direction, you can see the cells that depend on a data unit by using the trace dependent button. Finally, use the remove arrows button to clear the arrows. For your peace of mind, error checking will examine broken formulas and give you the reason for the error and possible solutions.<\/p>\n

Add-Ins<\/h2>\n

Because this is a Microsoft product, Excel has a huge database of add-in components that can do almost anything. There are add-ins that do SPSS level statistical analysis, interface with presentation software and analyze inventory amounts. Add-ins need to be downloaded in advance, then integrated with the Excel package. The add-in function is found under the file tab, and then under options. A dialogue window will pop up, and you should select click add-ins. From there, you will have a list of all\u00a0downloadable add-ins available<\/a>\u00a0to you.<\/p>\n

Flevy offers a robust add-in for PowerPoint called Flevy Tools<\/strong><\/a>. You can download a complimentary copy here<\/a>.<\/div>\n","protected":false},"excerpt":{"rendered":"

Microsoft Excel is powerful. You can use Excel to solve simultaneous differential equations, develop financial statements and run a statistical analysis on large data sets. The power of the program comes down to the user\u2019s creativity and the software\u2019s copious functions and formulas. With the introduction of Excel on the new\u00a0Apple iPad Air, you can… 4 Excel Functions Every Business Professional Needs to Know<\/span><\/a><\/p>\n","protected":false},"author":17,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[1],"tags":[499,497,498],"class_list":["post-1051","post","type-post","status-publish","format-standard","hentry","category-general","tag-excel-add-in","tag-microsoft-excel","tag-microsoft-office"],"_links":{"self":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/1051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/comments?post=1051"}],"version-history":[{"count":4,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/1051\/revisions"}],"predecessor-version":[{"id":1056,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/1051\/revisions\/1056"}],"wp:attachment":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/media?parent=1051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/categories?post=1051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/tags?post=1051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}