How to create a formula in Excel using the Function Library in Excel 2007 and Excel 2010

Updated on June 11, 2013

Introduction

Hi and welcome to my latest hub on Excel. The number of functions Excel offers can seem overwhelming at times and it is often difficult to know which function to use and once you know which function you want, it is not always easy to write a formula that works. Microsoft has created some excellent tools to help with this and we will look at these in this hub.

Today, I am going to look at using the built in Function Library to help you to create a formula in Excel quickly and easily. We will also look at Insert Function tool which allows you to search for a function, or to select a function from drop down lists. We will work through two examples, creating formulas using the MAX (Excel will return the largest number from your data range) and MID (Excel will return characters from the middle of a cell’s contents) functions.

Once you have selected the function you would like to use, if you use the Insert Function tool Excel then helps you to build your formula by providing you with dialogue boxes complete with instructions for each. It will even display the result of your formula for you so that you can adjust the parameters on the fly if you need to.

The Function Library in Excel 2007 and Excel 2010 logically groups functions together so that they can be easily found.
The Function Library in Excel 2007 and Excel 2010 logically groups functions together so that they can be easily found. | Source
The Insert Function tool in Excel 2007 and Excel 2010 which assists you to easily build formulas.
The Insert Function tool in Excel 2007 and Excel 2010 which assists you to easily build formulas. | Source

Using the Insert Function to search for the best function for you to use in Excel 2007 and Excel 2010

The Insert Function tool allows you to search for a function, or to choose one from a drop down list. In our first example, we have a short list of numbers and we would like Excel to tell us which of those numbers is the biggest.

Source data from which we will use a the Insert Function tool to find a function to tell us which cell contains the highest number in Excel 2007 or Excel 2010.
Source data from which we will use a the Insert Function tool to find a function to tell us which cell contains the highest number in Excel 2007 or Excel 2010. | Source

I select cell A10 and then click the Insert Function button which is in the Function Library group on the Formulas tab

Using the Insert Function tool's search capability to find the best function for our formula in Excel 2007 or Excel 2010.
Using the Insert Function tool's search capability to find the best function for our formula in Excel 2007 or Excel 2010. | Source

We don’t know what formula we want to use, so in the Search for a function box type in Biggest Number

Searching for the appropriate function for our formula using search in the Insert function tool in Excel 2007 or Excel 2010.
Searching for the appropriate function for our formula using search in the Insert function tool in Excel 2007 or Excel 2010. | Source
  • Click Go

Excel then comes back with some suggestions based on what we asked:

  • The first is DMAX which according to the description is used to return the largest number in the column of records in a database. As we don’t have a database, we will keep looking down the list.
  • The next function is MAXA. This function returns the largest value, but does not ignore logical values and text, so we will skip this too as we want it to ignore any cells containing text.
  • MAX is the next in the list. This also returns the largest value and does ignore logical values and text so we will choose MAX.

The Insert Function tool in Excel 2007 or Excel 2010 has enabled us to quickly and easily find the function that we need .
The Insert Function tool in Excel 2007 or Excel 2010 has enabled us to quickly and easily find the function that we need . | Source
  • Select MAX
  • Click OK

Excel now shows how clever the Insert Function tool is. Without me even typing anything in, it has completed the formula for me based on the cells nearby the cell containing the formula.

The Insert Function has auto-completed our formula in Excel 2007 or Excel 2010 based on the function we chose and the surrounding cells in our spreadsheet.
The Insert Function has auto-completed our formula in Excel 2007 or Excel 2010 based on the function we chose and the surrounding cells in our spreadsheet. | Source

Excel shows you the answer that your current formula will give you as well as explaining once more what the function does.

It also gives you an example of what to put into the Number1 field should you wish to change what Excel has already inputted for you.

If we click OK, you can see that we have created a formula using the MAX function without even typing anything in!

Formula created using the Insert Function tool in Excel 2007 or Excel 2010.
Formula created using the Insert Function tool in Excel 2007 or Excel 2010. | Source

Using the Function Library to create a formula in Excel 2007 and Excel 2010

Excel collects functions together in the library into a number of groups on the Formulas tab. These include:

  • Recently Used – Excel stores functions you have recently used here
  • Financial – e.g. NPV (Net Present Value), PV (Present Value)
  • Logical – e.g. IF, AND OR
  • Text – e.g. CONCATENATE (join together content from different cells) and LEN (find out the number of characters in a cell)
  • Date & Time
  • Lookup & Reference – e.g. CHOOSE and VLOOKUP
  • Math & Trig – e.g. SIN, TAN and DEGREES
  • More Functions – Includes Statistical (e.g. AVERAGE, MAX), Engineering, Cube and Informational (e.g. ISBLANK, ISERROR) functions

If you know which group your formula is in, you can open that group and then select the function you would like to use from the list.

Note: If you can’t find the function you are looking for, each group has the Insert Function tool we used above at the bottom of the list.

For this example, we will use the MID function from the Text group

We have the word Elephant in cell D4 and we want to extract some characters from the middle of it.

  • I click on D5 and then select the Text button from the Functional Library group
  • Select MID from the drop down list
  • Each dialogue box has an explanation of what is expected as we saw above with MAX
  • Select Text and then cell D4 which contains the text Elephant
  • Start_num is the position of the first character you want to extract; we will choose 4 for this
  • Num_chars is the number of characters you want to extract, we will again choose 4

Excel now displays the Formula result (which is phan in this example). If this is not the result we want, we can change any of the above boxes to change the result

  • Once you are happy, click OK

You can see once again, Excel has created the formula for us and the results are displayed in cell D5

Constructing a formula using the MID function using the Function Library in Excel 2007 or Excel 2010.
Constructing a formula using the MID function using the Function Library in Excel 2007 or Excel 2010. | Source
A completed formula in Excel 2007 or Excel 2010 created using the Function Library.
A completed formula in Excel 2007 or Excel 2010 created using the Function Library. | Source

Conclusion

To assist users in navigating the numerous functions that Excel has available, Microsoft has introduced two tools into Excel.

  • First, they have grouped all of their functions into logical groups to create the Function Library.
  • Secondly, within the Insert Function tool, you can search for the best function for your situation (we searched for biggest number in our first example)
  • Lastly, again with the Insert Function tool you can create a formula using the function of your choice. Each part of the formula is represented by a dialogue box complete with instructions to further assist you. You can even adjust the formula easily should the answer provided not meet your expectations

Both of these two tools allow you to create formulas simply and quickly. They allow you to harness the power of Excels wide array of functions without worrying about the syntax or their complexity when using them in formulas. Thanks for reading; I hope that you have found this hub useful and informative. Please feel free to leave any comments you may have below.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image
        Author

        Robbie C Wilson 5 years ago

        Hi Jabelufiroz,

        Thanks for your kind comment. I enjoy finding out how things work in Excel and figuring out how I can use them to make my work easier. I am pleased that you find my hubs useful.

      • jabelufiroz profile image

        Firoz 5 years ago from India

        An excel expert. Voted up and useful.

      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, turbofuture.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://turbofuture.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)