You can use the Analysis Toolpak add-in to generate descriptive statistics. For example, you may have the scores of 14 participants for a test. To generate descriptive statistics for these scores, execute the following steps. 1. On the Data tab, in the Analysis group, click Data Analysis. Note: can’t find the Data Analysis button? Click […]
This example teaches you how to create a histogram in Excel. 1. First, enter the bin numbers (upper levels) in the range C3:C7. 2. On the Data tab, in the Analysis group, click Data Analysis. Note: can’t find the Data Analysis button? Click here to load the Analysis ToolPak add-in. 3. Select Histogram and click […]
The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click […]
Reduced Cost | Shadow PriceSensitivity analysis gives you insight in how the optimal solution changes when you change the coefficients of the model. After the solver found a solution, you can create a sensitivity report. 1. Before you click OK, select Sensitivity from the Reports section. Below you can find the optimal solution and the […]
Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the combination of capital investments that maximizes the total profit. Formulate the Model The model we are going to solve looks as follows in Excel. 1. To formulate this binary integer programming (BIP) model, answer the following […]
Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the maximum flow from node S to node T in a directed network. Points in a network are called nodes (S, A, B, C, D, E and T). Lines in a network are called arcs (SA, SB, […]
Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the shortest path from node S to node T in an undirected network. Points in a network are called nodes (S, A, B, C, D, E and T). Lines in a network are called arcs (SA, SB, […]
Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost. Formulate the ModelThe model we are going to solve looks as follows in Excel. 1. To formulate this assignment problem, answer the following three questions. a. […]
Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the number of units to ship from each factory to each customer that minimizes the total cost. Formulate the Model The model we are going to solve looks as follows in Excel. 1. To formulate this transportation […]
Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems. Load the Solver Add-in To load the solver add-in, execute the following steps. 1. On the […]
Create Different Scenarios | Scenario Summary | Goal Seek What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily. Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price […]
Quickly format a range of cells by choosing a table style. You can also create your own table style. Quickly format a cell by choosing a cell style. 1. Click any single cell inside the data set. 2. On the Home tab, in the Styles group, click Format as Table. 3. Choose a table style. […]
Insert a Table | Sort a Table | Filter a Table | Total Row Tables allow you to analyze your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table. Insert a Table To insert a table, […]
Calculated Field | Calculated Item This example teaches you how to insert a calculated field or calculated item in a pivot table. Below you can find a pivot table. Go back to Pivot Tables to learn how to create this pivot table. Calculated Field A calculated field uses the values from another field. To insert […]
Refresh | Change Data Source Any changes you make to the data set are not automatically picked up by the pivot table. Refresh the pivot table or change the data source to update the pivot table with the applied changes. Refresh If you change any of the text or numbers in your data set, you […]
You can insert slicers in Excel to quickly and easily filter pivot tables. However, using the report filter gives the exact same result. Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot table. To insert a slicer, execute the following steps. 1. Click any […]
Insert Pivot Chart | Filter Pivot Chart | Change Pivot Chart Type A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other. Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot […]
Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the Analysis Toolpak to create a histogram. Remember, our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country. First, insert a pivot table. Next, drag the […]
Multiple Row Fields | Multiple Value Fields | Multiple Report Filter Fields It’s perfectly ok to drag more than one field to an area in a pivot table. We will look at an example of multiple row fields, multiple value fields and multiple report filter fields. Remember, our data set consists of 213 records and […]
Group Products | Group Dates This example teaches you how to group pivot table items. Learn how to group products and how to group dates by months. Below you can find a pivot table. Go back to Pivot Tables to learn how to create this pivot table. Group Products The Product field contains 7 items. […]
