June 20

0 comments

Insert an Excel function into your workbook

By Sharyn Baines

June 20, 2018

Functions

Learn how to insert an Excel function into your workbook.

Excel comes equipped with a large number of pre-defined formulas. These are known as functions.

There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few. 

I often get asked if I know them all. Um…no…definitely not. You’ll see when you work through this post just how many there are.  There are over 400!  Each created to make our lives a whole lot easier.

So, instead of us having to learn whopper calculations, Excel partially creates them for us. All we do is locate the function we want to use and insert the argument part of the formula.

Each function has a unique name and a set of arguments that are used to perform the calculation.

Insert a function into your workbook

The Insert function tool steps you through creating functions using the Formula palette. Use the Insert function tool until you become familiar familiar with the structure of a function. Once you are familiar you can type the formula yourself straight into a cell or in the formula bar.

1. Select the cell that will hold the calculation.

2. To open the Insert Function dialog box:

  • Click the Insert Function button on the Formula Bar.

Excel Insert Function

  • From the Formulas tab, in the Function Library group click the Insert Function button.
  • From the Home tab, in the Editing group click AutoSum and select More Functions from the list.

Excel Insert Function

 

The Insert Function dialog box will appear.

Excel Insert Function

 

3. To find a suitable function type a description of the calculation you want to perform in the Search for a function box and then click Go, or you can select a specific category from the select a category list.


Hint: to view all functions select the All category. This the best way to find a function until you become familiar with the functions included in each category. Functions that have been used recently are displayed in the Most Recently Used category.


4. Function names will be displayed in alphabetical order in the Select a function list box. Scroll to locate a function name or click into the list box and then press the first letter of the function to jump quickly to it in the list.

5. Select a function name. A description of the function is displayed in the lower-left corner of the dialog box.

6. Click OK.


Note: the following example is based on the SUMIF function.


The Formula palette opens. The name of the function is displayed in the upper-left corner. A description of the function is displayed in the lower part of the palette. Argument boxes will be displayed. As you place the insertion point into an argument box a description of the type of argument required is displayed at the very bottom of the palette.

 

Tip: to re-position the Formula palette, click and drag in the title bar.


Excel Sumif Function

 

In the previous example the function is calculating the total of a range of cells based on a set criteria. The result will appear in the lower left corner of the palette.

7. Click OK to finish your formula.

To edit a function simply change it in the Formula bar, of if you would like to see the Formula palette again, click on the cell that holds the function and then click the Insert Function button.

Excel Edit Function


Tip: once you become familiar with a function you can easily locate it on the Formulas tab, in the Function library. You may need to search each of the ‘books’ until you find the function. Clicking the function takes you directly to the Arguments box.

Excel Formula Tab


 

If you enjoyed this you may also enjoy...

How to Edit Drop Down List in Excel (find, add and remove items)

[Watch on YouTube] / [Subscribe to our YouTube Channel]Learn how to edit a drop-down list in Excel. In this blog you will learn how to find, remove and add items to the drop-down list. An extra step is also included for those using older versions of Excel.How to find items in a drop-down listClick onto the

Read More

How to Create Drop Down Menu in Excel (Drop Down List)

[Watch on YouTube] / [Subscribe to our YouTube Channel] Learn how to create a drop-down list in Excel. In this blog you will learn two different ways to create a drop-down list and the advantages of each as well as an extra step needed for older versions of Excel when creating a drop-down list from another

Read More

Remove Drop Down List in Excel

[Watch on YouTube] / [Subscribe to our YouTube Channel]Need to remove a drop-down list in Excel? In this blog you will learn how to remove the drop-down list as well as how to remove the menu items list.How to remove drop down list in ExcelClick onto the cell that has the drop-down list on it. In

Read More

How to Freeze rows in Excel

[Watch on YouTube] /  [Subscribe to our YouTube Channel]Would you like to keep row headings in Excel when you are scrolling so that you can easily identify your columns?  What if I told you there was a way to freeze rows and columns in Excel, so your headings come with you when you scroll down

Read More

Use Conditional Formatting to identify cells that are of interest

  Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using

Read More

Find, modify and break links to an Excel workbook

Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data

Read More

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>