How to Graph a Function in Excel? 2 Easy Ways
(Note: This guide on how to graph a function in Excel is suitable for all Excel versions including Office 365)
Time and time, Excel has proven to be a beneficial tool for performing various calculations. Excel offers calculations and simplifications on a vast amount of mathematical functions ranging from simple addition functions to complex quadratic, exponential, and trigonometric functions.
In addition to calculating the values, Excel also has the ability to provide a relationship between the input and output values. This representation in the form of graphs provides an easy way to compare and interpret the data.
In this article, I will show you how to use a function in Excel and how to graph a function in Excel using 2 easy ways.
You’ll Learn:
- What are Functions?
- How to Use Functions in Excel?
- How to Graph a Function in Excel?
- How to Customize the Graph?
Watch our video on how to graph a function in Excel
Related Reads:
VLOOKUP vs INDEX/MATCH vs XLOOKUP
How to Use Logical Functions in Excel ?
Logical Functions in Excel (IF, IFS, AND, OR, COUNTIF, SUMIF)
What are Functions?
Before we get to using a function, let us know what are functions.
Generally, functions are a set of calculations having a finite set of operations together with a variable to arrive at the output. In other words, they provide a relationship between the input and the output.
Let us see an example to know more about functions.
You all might have heard about the trigonometric functions like the Pythagoras theorem (c2=a2+b2) or quadratic equations like ax2+bx+c=0.
You all might have heard about the formula to calculate the radius of a circle. We use the formula πr2, where r is the radius of the circle and is a variable. For the different radii of the circle, the area will also be different.
In this case, the radius(r) is considered the input, and the area is the output. When it comes to plotting the points on a graph, the inputs are taken along the x-axis and the outputs are plotted along the y-axis.
How to Use Functions in Excel?
In Excel, you can use the functions to establish a relationship between the input and output easily. There are two ways to use functions in Excel.
1. By using Functions from Excel Library
If you are going to be operating on any mainstream function, Excel has a huge library of built-in functions you can choose from. You can just select the functions, enter the values and Excel gives you the output.
- Select a cell and enter any value. Let the input value be Angle and output value be Sine Value since we’ll be operating on a sine function. In this case, I have entered the value 0 in cell A4. You can also enter multiple values as inputs.
- Now, to add a function, click on any destination cell.
- Navigate to Formulas in the menu bar. Under Function Library, you will find a variety of categories consisting of different functions like Financial, Logical, Math&Trig, etc. Depending on your operation, choose the function from the Function Library.
If you are having a hard time trying to find the location of the function, click on Insert Function from the Function Library or from any of the dropdowns from the categories.
- This opens up a new Insert Function dialog box. You can choose the category and select the function you want. In case, if you are still unable to find the appropriate function, you can type the description and Excel shows you a list of related functions.
- Now that you have found the function, click Okay.
- This opens up another dialog box asking you to enter the arguments for the function. You can enter any constant value or if you want to select or add the name of the cell in the text box and click OK. In this case, I will pass the argument as A4 since this cell houses the value for the input.
- This gives the Sine value for the given input. Now, you can use the drag handle to perform the function on other cells too.
2. By Manually Entering the Function
There are some functions that might not be available in the Excel Function Library. In such cases, you can manually create a function and get the output.
Manually entering the function in Excel is very simple. Just add an “=” before the function in the destination cell and type them. In the case of variables, select or enter the cell number to get the output.
Consider the example of a quadratic equation y=4x2+2x+5. In this case, x is the input and y is the output to be calculated. To obtain multiple values for the quadratic function, add different inputs in different cells.
Enter the inputs in one column. Let it be called x. The inputs can be positive, decimal, negative, or even zero.
Here, the destination cell is B4. So in the place of x, now enter the function =4(A4)2+2(A4)+5 in the destination cell. Always remember to add “*” in place of multiplication when entering functions manually. Press Enter.
This gives you the output corresponding to the function and the input. You can use the drag handle to add the function to other cells and get a series of outputs.
Also Read:
How to Use AVERAGEIF in Excel? With 5 Different Criteria
IFERROR Excel-The Ultimate Guide to Catching Errors in Excel
How to Filter in Excel? A Step-by-Step Guide
How to Graph a Function in Excel?
Once you have the input and output for the required function, it is fairly easy to plot a graph.
- To plot a graph, select the x-axis(input) and y-axis(output) of the graph.
- Go to the Insert menu. Under Charts, select Scatter. Though there are ways to represent your data using other graphical representations like bar charts or pie charts, scatter represents the graph by specifying each point in the function.
- Click on the type of scatter chart to represent the data. This plots a graph for the function with the inputs and outputs along the x-axis and y-axis respectively.
How to Customize the Graph
When you click on the scatter chart, the graph usually populates in the center of the Excel sheet. You can move the graph to its desired places by clicking on the graph and dragging it. Move the pointer to the edges of the graph to resize the chart area.
You can also customize the chart by using the shortcut options which appear when you click on the chart.
Use the Chart Element option to add, show or hide any elements like headers, legends, and other projections.
Use the Chart Styles option to change the style and color of the chart. Changing the style and color gives the chart a more suitable appearance to present the data.
When your chart contains more than one data representation, you can use the Filter option to add or remove any data based on your preferences.
For in-depth and extensive customization of the chart, you can use the Chart Design and Format options in the Main menu.
Using the Chart Design option, you can change the layout of the chart, the color of the chart, switch axes, and move the chart between sheets.
Using the Format option, you can add any shapes, or text to add cues about the chart, align, and resize the chart.
Suggested Reads:
How to Add Leading Zeros in Excel? 4 Easy Methods
Excel String Compare – 5 Easy Methods
How to Hide and Unhide Columns in Excel? (3 Easy Steps)
Closing Thoughts
Plotting a function in the form of a graph gives an insight into the usage of the function easily interpret the data.
In this article, we saw how to use a function, and graph a function in Excel. We also learned how to customize the graph in Excel.
If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!