ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Top 5 Advanced MS Excel Functions That Will Make You an Expert User

Updated on December 18, 2017
Atanas Yonkov profile image

Atanas Yonkov is a web developer, blogger and an amid dreamer. In his spare time he enjoys hiking and playing the guitar.

MS Excel
MS Excel

MS Excel is one of the most popular data processing tools out there and there is a reason for this – this program can save you so much time once you learn how to use it properly. Check this article to see which are the most important advanced tips and tricks in Excel and why it is so important to learn them. If you are a beginner Excel user, I will advise you to read this article first: 20 Excel Formulas You Should Start Using Now.

Index - Match

This is a gorgeous MS Excel function that is similar to vlookup, however, it has much more functionalities. In fact, the only advantage of vlookup is that it is easier to learn. Once you step your foot into Index-Match, you will forget about vlookup. Now, let’s make a quick revision what vlookup can do. This is an Excel formula that lets you compare data from different tables, different sheets and even different workbooks. You can use this function to take data from one table and add it to another. Let’s have a look at the following example:

We have a table with employees, their position, the companies that they work for and their monthly salaries. We want to display on a separate cell where does any of the employees work.

Let’s say we want to check this info for cell A7 (Ralf Grutzen). The formula will return Soprano LTD, the company where the employee from cell A7 works for:

=VLOOKUP(A7,$A$1:$D$10,3,0)

We can accomplish the same result with index match. This formula will also return Soprano LTD:

=INDEX($C$1:$C$8,MATCH(A7,$A$1:$A$10,0))

What does the Index-match function do? As we can see, it has a different logic than vlookup. It actually works the other way around. It does not start with the determinant (the value we are using to get the result). Instead, it starts from the column with the results (C1:C8) and looks for a match in the determinant’s column. This property is one of the function’s biggest advantages over vlookup. We will find out shortly why.

Now, let’s try to use both formulas to answer the following question: Who is the employee of Soprano LTD? Try to do it with vlookup first. It doesn’t work, does it? The reason for this is that vlookup has very limited usage. You cannot get data from the left side of the determinant. When the determinant is in Column C (cell C7 in our case), you can only get data from columns D,E,F, etc. However, you cannot reach the data from the left side of the column. Now, let’s check if we can get the result with index-match.

=INDEX($A$1:$A$8,MATCH(C7,$C$1:$C$8,0))

The function returns Ralf Grutzen and this is the correct answer.

Conclusion: We have just found out that we cannot use vlookup to get values from the left-side of the look-up array. Instead, we should use index-match.

Index-match has a lot more advantages than vlookup and it is the preferred function for the Excel experts. Other reasons for that include:

  • The table can easily be expanded by adding new columns without breaking the formula. A new column can be added in the middle of the table, at the beginning or at the very end. The function should continue to work properly.
  • There is a lesser error rate when using index-match. The reason for this is that only two columns are used and there is no need to count the columns manually in order to construct the formula. This is especially important when workng with large files with a lot of columns
  • Unlike vlookup, you can use Index-match to make a horizontal lookup (hlookup). In vlookup, you can only look up vertically.
  • Last but not least, Index-match occupies significantly less computing power on the computer, so it is the mandatory way to search in larger tables with more than a thousand rows, as it works several times faster than vlookup.

Arrays

The array functions are advanced Excel functions, because you need to use data from more than one cell in order to return a result. Moreover, these functions are not entered as the other Excel functions. Normally, excel functions work when you press “enter” on your keyboard. However, array formulas do not work in this way. Instead, you should press “ctrl+shift+enter” to make them work.

The arrays are extremely useful when you want to multiply matrices or when you want to sum numbers from even/odd rows only. Let’s have a look at this example: we want to multiply the numbers from cells C and D. After that, we want to calculate the sum of their multiplied numbers.

We can use two ordinary functons to do this or we can use the following array formula:

=SUM(B1:B7*C1:C7)

One big advantage of that method is when we have a very large workbook with thousands of rows. Moreover, the risk of making mistakes is also lower, when using array functions. This is why I highly recommend you to start using them now.

Sumifs / Countifs

Sumifs and Countifs are another great functions that let you sum or count data based on multiple criteria. This can be very useful in many cases. Let’s have a look at the following example. We have a column with products, suppliers, quantity and delivery date. We want to know the total quantity of a specific product delivered by a specific supplier. To do that, we need to use the following function:

=SUMIFS(C2:C7,A2:A7,"apples",B2:B7, "George")

We can see that George has supplied 3 tons of apples in total.

Goal seek

Goal seek is an amazing financial instrument. It is an extremely helpful tool when working with financial data. It lets you determine how much you need to increase x, so you can reach a certain level of y.

Let’s have a look at this example. We have a table with sales for a certain amount of time. We want to find out how much sales we need to do for the next month, so we can reach a certain revenue threshold for the next quarter.

Our total revenues are 76,945 €. Let’s say we want to reach 100,000 € by the end of the next month. In order to estimate how much sales we need to generate in the next month, we should use the goal seek tool in Excel. To do that, go to data tab -> data tools group -> what if analysis -> go seek and set the parameters:

The goal seek function tells us we need to do 512 sales in the next month to reach that threshold.

XNPV and XIRR

If you are working in the corporate finance field, these functions are exactly what you need. They let you estimate the discounted cash flows and the net present value of a project. The XNPV formula lets you estimate the NPV by using the following parameters: the case flows, the dates to which the cash flows are discounted and the discount rate. You can check this tutorial for a detailed explanation how to use these functions: Why use the XNPV function in Excel?

This article aims to show the most important advanced Excel functions and tools that you can use to significantly improve your Excel skills. You can use these techniques to improve your user experience with the software, make your life easier, become more efficient and even to get a promotion.

© 2017 Atanas Yonkov

Comments

    0 of 8192 characters used
    Post Comment

    • BloomRule profile image

      Rube Bloom 

      6 months ago from Manila, Philippines

      Thanks so much. These functions are very useful. I'm going to try them out.

    • officesetup-offic profile image

      Mir Ariif 

      11 months ago from 9303 Shady Lake Dr. 102Q Streetsboro Ohio 44241

      Nice Post !

      More information about Office Setup To get started with your Microsoft Office Installation you must need valid product key code & visit www.officesetup-officesetup.com and we can also help you with your entire process to setup office product online. Call now +1-844-777-7886

    • Atanas Yonkov profile imageAUTHOR

      Atanas Yonkov 

      11 months ago from Bulgaria

      Thank you so much, Stella!

    • stelaligizaki profile image

      Stella Aligizaki 

      11 months ago from Greece

      Very interesting article. I often use excel. Your tips are helpful. The link "20 EXCEL FORMULAS YOU SHOULD START USING NOW" is also clear!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)