How to make a football field in Excel

– Select the cell under the “Min Point” column.
– Press the (equal sign) button on your keyboard and simply select the cell containing the Min value. For instance, it is “cell I34”.
– To calculate the “25^th Point”, subtract the “25^th Percentile” value from the “Min value”.
– Choose the cell corresponding to the “25^th Point” and extend the formula horizontally by dragging the fill handle to calculate the values for the “Median Point”, “75^th Point”, and “Max Point”.
– Afterward, choose the cells that hold the calculated results and paste them into the cells below.

– Select the range of cells from “Min Point” to “Max Point”.
– Then, navigate to “Insert Tab” and locate “Bar chart” in the “Charts” group.
– After that, click on the “Bar chart” and insert a Stacked Bar Chart by clicking on it.
– Then Position the Bar Chart anywhere you like.

– Click inside the chart area to select it.
– Then, right-click on the chart and a context menu will appear.
– After that, click on “Select Data” and it will open a dialogue box called “Select Data Source”.
– Click on the “Edit” option under “Horizontal (Category) Axis Labels”.
– Then, select the range containing all valuation metrics.
– Now, click on “OK” and these valuation metrics will appear in the Chart.

– Click on the Bar to select it.
– Right-click on it and a context menu will appear.
– After that, click on “Format Data Series” and it will open a sidebar.
– Go to the fill options and select the “No Fill” option.
– We have hidden the bars the bars except the Bars showing data from 25^th Point to 75^th Point but we didn’t include the bar of column “25^th Point” here because it is actually distance between “Min Point” and “25^th Point”.

– Click on the Bar to select it.
– Right-click on it and a context menu will appear.
– After that, click on “Format Data Series” and it will open a sidebar.
– Go to the fill options and select the “Solid Fill” option.
– Then, fill any color you like.

– Select any empty cell in the sheet and enter the chart title there.
– Now, click on the Chart title to select it.
– Then, go to the formula bar and press the = (equal sign) button on your keyboard.
– After that enter the cell name in which we entered the title for the chart and press Enter to change the Chart title.
– This will update the Chart title and it will benefit you to change the chart title by editing the cell’s text.

– Delete the unwanted Legends by clicking on them and pressing the delete button.
– For changing the position of Legend Entries, navigate to the “Chart Design Tab”.
– Then, click on “Add Chart Elements”.
– Hover your mouse over the Legend option and select the position of the Legends on Chart from the drop-down menu.

– Click on the Legend Entries and increase their size for better readability.
– Click on the Legends to select them.
– Then, right-click on the chart and a context menu will appear.
– After that, click on “Select Data” and it will open a dialogue box called “Select Data Source.
– Click on the legend that you wish to rename. For instance, we clicked on series 3.
– Then, click on Edit and enter the series name to change it.
– Now, click “OK” and the Legend will be renamed.
– Repeat this process for all legends.

– In the “Current Share Price” row, select the first cell and enter an equal sign there.
– Then, enter the cell address of the cell which contains the current share price value.
– Now, press the F4 button to lock this value when we extend it.
– Use the fill handle and extend this to the adjacent cells under the “Max” column.
– In the cell under the “Min Point” column type 1000 and type dummy values such as zeros in cells afterward till the cell under the “Max Point” column.

– Right-click on the Chart and click on the “Select Data” option.
– In the “Select Data Source” Dialogue box, click on Add an option under the “Legend Entries (Series)”.
– It will open another dialogue box.
– Enter the cell containing the text “Current Share Price” in the “Series name” box.
– Enter the range of cells containing Current Share Prices in the “Series value” box.
– Then, click on “OK”.

– Click on the yellow bars that have just appeared in Chart.
– You will see the formula given below in the formula bar.

=SERIES(‘FootBall Field’!$B$30,,’FootBall Field’!$I$52:$M$52,6)

– Now, we have to manually edit the series formula so that it has both “x” and “y” values.
– Remove one comma from double commas (,,) that are present after the first cell reference “$B$30” in the formula.
– Now, remove the parenthesis, 6 from the end of the formula.
– Then, select the dummy values that we entered.
– After that, enter a comma and then type 1.
– After following all the steps, your formula would look like this.
=SERIES(‘FootBall Field’!$B$30,’FootBall Field’!$I$52:$M$52,’FootBall Field’!$N$52:$R$52,1)
– Then, press Enter.

– Right-click on the yellow bar on the Chart.
– Click on “Change Series Chart Type”.
– Then, change the Chart type of “Current Share Price” to “XY Scatter with smooth lines”.
– Then click on “OK” and a line showing the “Current Share Price” will appear.

– By following the above step, the valuation metrics have disappeared by default.
– To restore them, click inside the chart area to select it.
– Then, right-click on the chart and a context menu will appear.
– After that, click on “Select Data” and it will open a dialogue box called “Select Data Source”.
– Click on the “Series 1” under “Legend Entries (Series)”.
– Click on the “Edit” option under “Horizontal (Category) Axis Labels”.
– Then, select the range containing all valuation metrics.
– Now, click on “OK” and these valuation metrics will appear again in the Chart.

– Right-click on the secondary axis and select the “Format Axis” from the context menu.
– It will open a sidebar on the right side of the screen.
– You will see the “Bounds” option under the “Axis Options”.
– Enter “0” in the minimum value and “1000” in the maximum value.
– Then press Enter button on your keyboard.
– The line will automatically extend to an appropriate length.

– For removing the “Secondary Axis”, navigate to the “Chart Design Tab”.
– Then, click on “Add Chart Elements”.
– Hover your mouse over the “Axes” option and click on the “Secondary Vertical” from the drop-down menu.
– This will remove the “Secondary Axis” providing the football chart a cleaner look.

– For adding Data Labels for the valuation metrics in the Chart, we would have to insert Text Boxes.
– Navigate to the “Insert” Tab and look for the command button name “Text Box” in the “Text” group.
– Now your cursor will change its shape.
– Click and drag on Chart to make a Text box that will work as Data Label.
– Type the text in your text box according to your requirements.

– Change the “Current Share Price” and you will see that the “Current Share Price” line on the Chart changes with the change in “Current Share Price”.
– For example, if the company share price drops from $96.54 to $58.62, the line will also shift its position automatically.