When you create a line graph in Excel, the lines are angled and have hard edges by default. You can easily change this to a curved graph with nice, smooth lines for a more polished look. We’ll walk you through the process step by step to convert your graph.
In this example, we want to create a curved line graph from this data on cookie sales.
Select and highlight the range A1:F2 and then click Insert > Line or Area Chart > Line.
The line graph is inserted with straight lines corresponding to each data point.
To edit this to a curved line, right-click the data series and then select the “Format Data Series” button from the pop-up menu.
Click the “Fill & Line” category and then check the box for “Smoothed Line.”
Using a smoothed line can help make your line graphs look smarter and more professional.
Smoothed lines can also be a clever way of distinguishing one data series from another. For example, targets from actuals or last year to this year.
- How to Find the Tangent on a Graph in Excel
- How to Get the Area Between Curves in Excel
- How to Graph Simultaneous Differentials in Excel
- In What Ways Is MS Excel Used to Interpret Data?
- How to Use Graph Callouts in PowerPoint
- How to Create the Average Function to Calculate Average Projected Revenue in Excel
The world in which your business operates is constantly changing. Capturing the state of your business once a month may not reflect the reality of your situation. Fortunately, in Microsoft Excel you can add fitted curves and trendlines to help you track gradual changes in your business conditions rather than only large shifts.
Open an existing Microsoft Excel spreadsheet containing the data you wish to graph and fit to a curve, or create a new spreadsheet and enter your data.
Open the “Insert” tab and select “Chart” to create a chart containing the individual data point on which you will plot the fitted curve. Common formats include the XY Scatterplot, Column and Area.
Select the data columns in your spreadsheet corresponding to the X (dependent) and Y (independent) values of the data. The X-axis commonly corresponds to time and the Y-axis commonly corresponds to a value such as sales, cost, profit or revenue.
Open the “Chart” menu and select “Add Trendline.” This will plot a line showing the trend of the data on the graph calculated using a moving average or similar algorithm. This helps you see how the data evolves over time without the effort and constraint required to fit the data to a statistical model. Select and then right-click on the trendline to change the format.
Select the “Display equation on chart” option under the Fit Trendline dialog box to change the model used to calculate the trendline. You can choose from lineal, exponential, logarithmic and polynomial models to match your data. Least-squares curve fitting is used to calculate the optimal model fit to the data.
- Microsoft Office: Create a Chart From Start to Finish
- Newton Excel Bach: Using LINEST for Non-linear Curve Fitting
Chris Daniels covers advances in nutrition and fitness online. Daniels has numerous certifications and degrees covering human health, nutritional requirements and sports performance. An avid cyclist, weightlifter and swimmer, Daniels has experienced the journey of fitness in the role of both an athlete and coach.
By Madhuri Thakur
Excel Normal Distribution Graph (Table of Contents)
Normal Distribution Graph in Excel
A normal distribution graph in excel is a continuous probability function. It is a common method to find the distribution of data. A formula has been found in excel to find a normal distribution which is categorized under statistical functions. This is completely depending on the mean and standard deviation. Normal distribution returns for a specified mean and standard deviation. It is a built-in function for finding mean and standard deviation for a set of values in excel. To find the mean value, the average function is being used. The normal distribution will calculate the normal probability density function or the cumulative normal distribution function. The graphical representation of this normal distribution values in Excel is called a normal distribution graph.
Excel functions, formula, charts, formatting creating excel dashboard & others
How to Calculate Normal Distribution in Excel?
Below is the data are given with some student’s name and the mark obtained by them in a particular subject.
By using this, let’s try to find the normal distribution. To find the normal distribution, we need two more data that is the mean and standard deviation. To find the mean, please apply the average function.
- Here we applied the formula =AVERAGE (C2:C15) where column C consists of the marks of each student.
- You will get the mean value of the given data as below.
- The standard deviation is calculated by using the formula =STDEV(C2:C15).
- You will get the standard deviation value of the given data as below.
Now for the Normal distribution graph in excel, we have the mean and standard deviation of the given data. By using this, we can find the normal distribution.
The normal distribution function is a statistical function that helps to get a distribution of values according to a mean value. This will help to find the variation of the values among a data set. This can be calculated by using the built-in formula.
- X: Defines for which value you want to find the distribution.
- Mean: The arithmetic means value for the distribution.
- Standard_dev: The standard deviation for the distribution.
- Cumulative: This is a logical value. A true indicates a cumulative distribution function, and a false value indicates a probability mass function.
Here we will find the normal distribution in excel for each value for each mark given.
Microsoft Excel is designed to organize and interpret small and large amounts of data. Building a curved graph in Excel based on X and Y data points is fairly simple. You begin by adding the data points. After adding each data set, you can set the line graph to bend and curve according to the data. The function simply works around the horizontal and vertical axis to form the graph.
Drawing a Circle
If pinpoint accuracy is not important, you can simply draw a curve shape to create a graph. Excel offers options for drawing a variety of shapes with circles, curves and straight lines. To draw a simple curve shape without perfectly adhering to data points, click Insert from the primary menu. Choose Shapes to retrieve all of the line drawing options and select the curve shape.
Click on the point where you want to begin the curve. Hold down the cursor and drag to draw your line. Continue holding until you hit the end point on your line drawing. The curve will show as you draw but will not finish until you release the cursor and click again on the end point. Repeat with an inverted curve if you want to create an S curve in Excel.
Create a Curve Graph
Open a new workbook and label the first column as X for the horizontal axis and the second column as Y for the vertical axis. Input your data into each column and double check the results to ensure everything is properly entered. Errors will change the course of your graph and make it display a shape different from the desired result.
Click on the A1 cell and drag down and across the A and B columns to highlight all of the data available. You are essentially selecting all data and can also perform a select-all function so long as both columns are selected at the same time.
Select Insert to open a window of options and choose Charts to open the chart and graph options. Click on XY Scatter to generate a curved graph in Excel. A window of options will open to setup the final graph. Make sure you have mapped Column A to the X Axis and Column B to the Y Axis.
It will also show a set of design previews for the graph. Some only have a scatter plot with all of the points while others show the line through the points. Choose the curved line graph unless another option is desired.
The Purpose of Scatter Plots
Scatter plots are an excellent method of comparing the correlation between two sets of data. They essentially show how one set of data influences the other. The applications for a graph of this nature are endless. In business, the plot is generally applied to cause-and-effect situations.
For example, a business might plot a course that shows the revenue resulted against sales call volume in a localized area. They may discover that an increase happens until a certain point where the revenue tapers despite a continual increase in calls. The business can then decide to expand the area, change the script or take alternative measures at the inflection point.
Correlating between any activity and revenue is commonplace in business. The chart can also work to correlate between productivity and revenue or any set of variables that will influence the business.
Enter Data for Cases in Rows
Enter the cases into the spreadsheet, listing the data for each case in a separate row. One column should be designated for the date of onset of disease for each case. In the example spreadsheet below, I deleted the names of the cases from column A, but I listed the date of onset in column B (so this simple example just has one column for date of onset).
Sort by Date
Next sort the data set according to date of onset of disease. You can do this by “selecting” the block of data (columns and rows) that you want to sort using your mouse (click the left mouse button on the upper left hand cell of the block you want to sort and hold the mouse button down as you move the mouse down and to the right all the way to the lower right hand cell of the block you want). Then release the mouse button. This should highlight the block of data to be sorted. Then, from the menu at the top of the page click on “Data” and then select the “Sort” option.
This makes a dialogue box appear. Make sure that the selection “Header row” is selected at the bottom of the box if the columns have header titles. Then at the top of the dialogue box click on the selection arrow for “sort by” and chose “Date of onset”. Then click on “Ok”. The block of data should now be sorted by date.
Tally the Number of New Cases Each Day
Once the list is sorted by date, it easy to count up how many people became ill on a given date (step 2 in the spreadsheet illustration).
Sometimes it is more convenient to tally the number of new cases that occurred at intervals other than 1 day. For example, the illustration below tallies new cases that occurred at 4 day intervals after 4/28, when the outbreak began.
The spreadsheet in Excel with the so-called ABC curve allows the user to make the inventory control your company. Through the data in this spreadsheet, the manager can make a comparison between the items and determine which ones are relevant and which are disposable for increasing the company’s revenue.
Do not you know the ABC curve? You do not perform a comparison to control your stock? Know that using this tool is very important to reduce the costs of your business.
The origin of the ABC curve is a method based on theories of the Italian economist Vilfredo Pareto. It is about dividing the items of your stock into 3 groups according to their importance: A, B or C. In group A are the most important items. In group B are the intermediate level items. In group C are the items with low relevance.
Stock management with the ABC curve in Excel
We elaborated a step by step how to make an ABC curve worksheet in Excel. Follow:
Creating the 4 First Columns
Step 1: Initially we will create four columns and enter the values. Are they:
- First Column: A1-A11: It is the code of each product in your stock.
- Second Column: B1-B11: Quantity of product in stock.
- Third column: C1-C11: Unit value of each product.
- Fourth Column: D1-D11: Total value of the products.
Step 2: To complete the data in the fourth column, multiply the value of column B with the value of column C for the respective product. For example: To get the value of the D2 column, use the formula = B2 * C2. Therefore, the quantity of products multiplied by the unit value will result in the amount spent in your company for that stock item.
Step 3: After that, put the values in the D2 column in descending order.
Step 4: In sequence, in the D12 cell use the formula = SUM (D2: D11). The value will represent the amount of the amount spent in your company’s current inventory.
Step 5: Create the% column in cell E1. The values of cells E2 through E11 represent the percentage that each value of Column D (cells D1 to D11) in relation to the value of cell D12.
Use the respective formulas in each cell below:
- Cell E2: = D2 / D12
- Cell E3: = D3 / D12
- Cell E4: = D4 / D12
- Cell E5: = D5 / D12
- Cell E6: = D6 / D12
- Cell E7: = D7 / D12
- Cell E8: = D8 / D12
- Cell E9: = D9 / D12
- Cell E10: = D10 / D12
- Cell E11: = D11 / D12
Step 6: After that, select the E2 cells up to E11 and click Home / Number /%.
Step 7: In sequence, in the E12 cell use the formula = SUM (E2: E11). If the result is 100%, the worksheet is correct until now. Otherwise, check the step-by-step again to notice the error and correct the error.
Inserting% Accumulated column
Step 8: Create the% Accumulated column in the F1 cell. The values of the F2 cells to F11 represent the sum of the percentages of the% column.
Use the respective formulas in each cell below:
- F2 cell: = E2
- F3 cell: = F2 + E3
- F4 cell: = F3 + E4
- F5 cell: = F4 + E5
- F6 cell: = F5 + E6
- F7 cell: = F6 + E7
- F8 cell: = F7 + E8
- F9 cell: = F8 + E9
- F10 cell: = F9 + E10
- F11 cell: = F10 + E11
Step 9: Once this is done, select cells F2 through F11 and click Home / Number /%.
Inserting the graph with the ABC curve
Step 10: Select the values of the A2 cells up to A11.
Step 11: Holding down the CTRL key also select the values of the F2 cells to F11.
Step 12: Select the chart: Insert / Graph / Rows / Line 2d / Rows.
Step 13: Select the 1 Series caption and then click DELETE.
Step 14: The percentage axis is between 0 and 120%. To leave between the values of 0 and 100% select the axis and click on Format axis.
Step 15: In axis options, select FIXED for minimum and maximum. Respectively leave them at 0,0 and 1,0. Click CLOSE.
Through these steps the worksheet will be ready. You can change colors and leave it any way you like.
Analyzing the spreadsheet data with ABC curve
Taking into account 3 groups A, B and C, we chose the reference 70-20-10.
That way group A represents 70% of total costs, group B 20%, and group 10%.
Taking into account the data of this worksheet, the groups are represented:
- GROUP A: 3 products (9, 4 and 10 codes);
- GROUP B: 2 products (5 and 8 codes);
- GROUP C: 5 products (1, 3, 6, 7 and 2 codes).
- 30% of products represent 70% of total costs;
- 20% of products represent 20% of total costs;
- 50% of products represent 10% of total costs.
This data is of fundamental importance for you to maintain a controlled inventory and perform an adequate management for perfect investments.
Did you like the spreadsheet? Has the analysis of a spreadsheet with ABC curve in excel already helped you to control your stock and make correct investments? Leave your comments regarding the experience with this spreadsheet.
A “bell curve” is the nickname given to the shape of a normal distribution, which has a distinct “bell” shape:
This tutorial explains how to make a bell curve in Excel for a given mean and standard deviation and even provides a free downloadable template that you can use to make your own bell curve in Excel.
Example: Bell Curve in Excel
Use the following steps to make a bell curve in Excel.
Step 1: Create cells for the mean and standard deviation.
Step 2: Create cells for percentiles from -4 to 4, in increments of 0.1.
Step 3: Create a column of data values to be used in the graph.
Step 4: Find the values for the normal distribution pdf.
Step 5: Create x-axis plot labels for only the integer percentiles.
Step 6: Make the bell curve.
First, highlight all of the values in the pdf column:
Then, in the Charts group on the Insert tab, click the first plot option in the Insert Line or Area Chart category:
A bell curve will automatically appear:
Step 7: Modify the x-axis labels.
Right click anywhere on the chart and click Select Data. A new window will pop up. Click on the Edit button under Horizontal Axis Labels:
Select the range of cells where the x-axis labels are located. In our case, it’s cell range D5:D85. Then click OK.
The x-axis labels will update automatically :
You’ll notice that if you change the mean and standard deviation, the bell curve will update automatically. For example, here’s what the bell curve turns into if we use mean = 10 and standard deviation = 2:
Feel free to modify the chart title, add axis labels, and change the color if you’d like to make the chart more aesthetically pleasing.
Feel free to download this free template that was used to make the exact bell curve in this tutorial.
This example teaches you how to create a Pareto chart in Excel. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes. In this example, we will see that roughly 80% of the complaints come from 20% of the complaint types.
Excel 2016 or later
To create a Pareto chart in Excel 2016 or later, execute the following steps.
1. Select the range A3:B13.
2. On the Insert tab, in the Charts group, click the Histogram symbol.
Note: a Pareto chart combines a column chart and a line graph.
4. Enter a chart title.
5. Click the + button on the right side of the chart and click the check box next to Data Labels.
Conclusion: the orange Pareto line shows that (789 + 621) / 1722 ≈ 80% of the complaints come from 2 out of 10 = 20% of the complaint types (Overpriced and Small portions). In other words: the Pareto principle applies.
If you don’t have Excel 2016 or later, simply create a Pareto chart by combining a column chart and a line graph. This method works with all versions of Excel.
1. First, select a number in column B.
2. Next, sort your data in descending order. On the Data tab, in the Sort & Filter group, click ZA.
3. Calculate the cumulative count. Enter the formula shown below into cell C5 and drag the formula down.
4. Calculate the cumulative %. Enter the formula shown below into cell D4 and drag the formula down.
Note: cell C13 contains the total number of complaints. When we drag this formula down, the absolute reference ($C$13) stays the same, while the relative reference (C4) changes to C5, C6, C7, etc.
5. Select the data in column A, B and D. To achieve this, hold down CTRL and select each range.
6. On the Insert tab, in the Charts group, click the Column symbol.
7. Click Clustered Column.
8. Right click on the orange bars (Cumulative %) and click Change Series Chart Type.
The Change Chart Type dialog box appears.
9. For the Cumulative % series, choose Line with Markers as the chart type.
10. Plot the Cumulative % series on the secondary axis.
Note: Excel 2010 does not offer combo chart as one of the built-in chart types. If you’re using Excel 2010, instead of executing steps 8-10, simply select Line with Markers and click OK. Next, right click on the orange/red line and click Format Data Series. Select Secondary Axis and click Close.
12. Right click the percentages on the chart, click Format Axis and set the Maximum to 100.
Conclusion: the Pareto chart shows that 80% of the complaints come from 20% of the complaint types (Overpriced and Small portions). In other words: the Pareto principle applies.
- How to Make a Chart From Two Different Sheets in Excel
- How to Graph Simultaneous Differentials in Excel
- How to Do a Two-Sided Graph in Excel
- How to Create a Pie Chart With Google Spreadsheet
- How to Put Numbers in Numerical Order in Excel
- How to Make One Long Column Into Multiple Columns in Excel
Representing an indifference curve in a graph helps you visualize consumer indifference between different product bundles. You can create an indifference map to indicate what amount of goods or bundles of goods that the consumer must sacrifice in order to consume more goods. Plot the indifference curves by entering your data sets into columns in Microsoft Excel and creating a new Scatter chart. Adding a new series to the chart enables you to compare multiple consumer indifferences for specific products.
Open the spreadsheet containing your data in Excel — or create a new blank spreadsheet.
Click inside the first cell and enter the smallest data amount for your first product bundle preference.
Click inside the next cell in the column and enter the second smallest data amount. Work your way down the column from smallest to largest until you have entered all of the data for the first product preference.
Click inside the first cell of the next column and enter the largest data amount first.
Click into the next cell in the column and enter the second largest data amount repeating the process from the first column but in reverse order — largest to smallest.
Enter data for additional product preferences into separate column sets and repeat the process — entering data from smallest to largest in the first column in the set, and then in reverse order for the next column in the set.
Click on the “Insert” ribbon tab, click the “Insert Scatter (X,Y) or Bubble Chart” button in the Charts group, and then click “Scatter with Smooth Lines.” Excel automatically generates a scatter chart using the data from your spreadsheet — you will need to manually re-select the correct data for your scatter chart.
Click on the “Select Data” button in the Data group of the Design ribbon. Click the “Remove” button for each series to remove them from your chart.
Click the “Add” button, and then enter a label for your first data set into the “Series Name” field of the Edit Series dialog box. Alternatively, you can click on a cell in your spreadsheet containing the label for your first data set.
Click into the “Series X Values” field and then click and drag to select all the data from the first column in the data set. Repeat the process with the “Series Y Values” field, selecting all of the data in the second column of the data set. If prompted, click the “OK” button and manually delete the “<1>+” text from the “Series Y Values” field. Click the “OK” button to save the series to your chart.
Click the “Add” button and repeat the process of selecting the data from your second data into the appropriate fields. Click the “OK” button once you have finished selecting the second data set.
Click the “OK” button to save changes to the series and close the “Select Data Source” window. Your scatter chart now represents the indifference curves for your consumer product preference.