Remove the total row from the table and then clear the Sport filter.Īdd Gradient Fill Blue Data Bars to the range A14:A19. Display a Total Row in the table, and then sum the Quantity in Stock for Hiking items. Filter the table on the Sport column to display only the Hiking types. Copy the function down through cell G19.įormat the range A13:G19 as a table with headers and then apply Table Style Light 13. If the Quantity in Stock is less than 50, then the cell should display Order. On the Portland Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. Format the range B5:B8 with Accounting Number Format. In cell B8, enter a function that will calculate the highest retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B6, enter a function that will display the median retail price. On the Portland Inventory worksheet, in cell B5, enter a function that will display the average retail price. Insert a Custom Footer with the File name in the left section and then save your workbook.
Set all of the Width and Height boxes to 256 and then change the Material to the fourth Standard type-Metal. From the 3-D Format gallery, modify the 3-D options by changing the Top bevel and Bottom bevel to the first bevel option in the first row.
#HOW TO MAKE A PIE CHART IN EXCEL WITH NON ADJACENT RANGES SERIES#
Select the entire pie and display the Format Data Series pane. Change the Font color to White, Background 1. Change the data labels Font Size to 14 and apply Bold and Italic. Remove the Legend from the chart, and then add Data Labels that display only the Category Name and Percentage positioned in the Center. Name the sheet Net Sales Chart.Ĭhange the Chart Title to Annual Net Sales by State and then change the chart title Font Size to 32. Insert a 3-D Pie chart, and then move the chart to a New sheet. To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals. As the chart title, type Quarterly Net Sales by State Align the upper left corner of the chart inside the upper left corner of cell A11, and then size the chart so that its lower right corner is slightly inside cell H24. Click the first Clustered Column chart that uses the state names as the category axis.
Select the range A3:E8, and then use the Recommended Charts command to suggest an appropriate chart. Add Markers and apply Sparkline Style Colorful 4. In the range H4:H8, insert Line sparklines to represent the trend of each state across the four quarters. Fill the formula down through the range G5:G8. Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Texas Total is of Total Sales, and then apply Percent Style. Apply the Total cell style to the range B9:F9. On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Open the downloaded Excel file named GO_XL_Grader_Vol1_CAP_v2.xlsx. Additionally, you will create a summary sheet, format cells, insert charts, insert sparklines, and create a table in a workbook.įor the purpose of grading the project you are required to perform the following tasks: In this project, you will work with multiple worksheets and enter formulas and functions to calculate totals, averages, maximum values, and minimum values.
Firstly you will need to setup your data to be able to compare month to month revenue.