How to create a matrix in ExcelFirst thing to do is to know the size of your matrix that you want to create in Excel. Show
Let's take a 3 by 3 matrix. Enter the number of the matrix. Then name it in the top left corner. Like here M2M. A basic operation on a matrix in ExcelIf you want to add, substract, multiply or divide or do any basic math operation to a excel matrix. Select a range of the same size as the matrix. Type for example =M2M+1 You follow this by SHIFT + CTRL + ENTER and this will create the new matrix like here under. The most important when working with matrixes in Excel is to remember to select the area and to finish the operation with SHIFT + CTRL + ENTER. If you want to add 2 matrixes you can for example do =M2M+E1:G3 followed by SHIFT + CTRL + ENTER. Now the basic functions Excel Function MDETERMThe MDETERM() function is used to calculate the determinant of a square matrix. A square matrix is the one which has same number of rows and columns. Consider a sample matrix : The determinant of the above matrix is calculated using the formula: determinant= ad - bc Excel Function MINVERSEThe MINVERSE() function is used to calculate the inverse of a square matrix. The inverse of the square matrix is also a square matrix with identical number of rows and columns. The result is rounded to 16 decimal places. Again, to create a matrix you select all the members of a matrix. then you type the formula like the one below and type CTRL - SHIFT - ENTER instead of ENTER the parenthesis sign is created automatically Excel Function MMULTThe MMULT() function is used to determine the product of two matrices. The product of matrices is possible only if the number of rows in matrix1 is equal to the number of columns in matrix2. This function takes two arrays as its arguments. You can find similar Excel Questions and Answer hereunder1) basic math functions gcd lcm mround multinomial sqrt sqrtpi 2) basic math functions ceiling combin exp fact floor 3) Various important math functions in VBA (trigonometry, algebra, exponential) 4) basic math functions degrees radians rand 5) Is there a way I can round the sales figure to the nearest 500? 6) basic math functions hyperbolic functions sinh cosh tanh csch sech coth 7) basic math functions log log10 roman trunc 8) basic math functions asin acos atan acot 9) basic math functions asinh acosh atanh acoth 10) basic math functions sin cos tan csc sec cot If you are looking for ways to create a matrix chart in Excel, this article is for you. A matrix chart is very useful for showing multiple series of data easily in a graph. So, let’s start with the main article to know the details of the procedures to create a matrix chart. Download Workbook2 Ways to Create a Matrix Chart in ExcelHere, we have the records of the selling prices, cost prices, and profits of some of a company’s products. By using this data range we can create 2 types of matrix charts; Bubble Matrix Chart, and Quadrant Matrix Chart. In this article, we are going to illustrate the necessary steps for creating these 2 types of charts. We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience. Type-01: Create a Matrix Bubble Chart in ExcelThe procedures for creating a Matrix Bubble Chart will be discussed in the following steps of this section. The selling prices, cost prices, and profits of the 5 products; Orange, Apple, Kiwi, Walnut, and Raspberry will be arranged through the bubbles in this chart to visualize them easily. Step-01: Creating Additional New Data RangesTo create 5 different series for the 5 available products in the Bubble chart we will need 2 additional ranges. ➤ For the Additional Range 2 after entering the product names in the first column, you have to add 3 extra columns (as we have 3 sets of values in the Selling Price, Cost Price, and Profit columns). Make sure that the serial numbers in these columns are arranged in reverse orders. Step-02: Inserting Bubble Chart to Create a Matrix Chart in ExcelIn this step, we will insert a Bubble chart
for the 3 sets of values and then rearrange the bubbles with the help of the two additional ranges. After that, the following Bubble chart will be created. ➤ To rearrange the bubbles select the chart and right-click on it. ➤ Then choose the option Select Data from various options. After that, the Select Data Source dialog box will open up. ➤ After removing Series1 click on Add to include a new series. Then the Edit Series wizard will pop up. ➤ Series bubble size will be the selling price, cost price, and profit of the product Orange and then press OK. In this way, we have added a new series Series 1. ➤ For Series X values select the serial numbers of the Additional Range 1 and then for Series Y values select the serial numbers in the three columns of Product Apple of the Additional Range 2. Then the new series Series2 will appear. Similarly, complete all of the 5 series for the 5 products and press OK. Then you will get the following Bubble chart. Step-03: Removing by Default Labels of Two AxesAfter rearranging the bubbles in the chart we will have some default labels that will not be used in this chart so we have to remove them. ➤ Choose the option Format Axis. After that, the Format Axis pane will appear on the right side. ➤ From various options choose None. Then the Label Position will be changed to None. In this way, we have removed the labels of the X-axis and do this similar process for Y-axis also. Finally, we have discarded all of the default labels from the chart. To add our desired new labels for this chart we will add two extra ranges in this step. ➤ Similarly, create the Additional Range 4 for the labels of the Y-axis. Here, the first column contains 0, the second column contains the serial numbers in reverse order and the last column is for the bubble widths which is 0.001. Step-05: Adding New Series for Labels to Create a Matrix Chart in Excel➤ To add the new 2 series to the chart Right-click on the chart and then choose the Select Data option. ➤ Click on Add in the Select Data Source dialog box. After that, the Edit Series wizard will pop up. In this way, we have created the new series Series6 and now press Add to enter another series. ➤ In the Edit Series dialog box, for Series X values select the first column
of the Additional Range 4, for Series Y values select the second column and choose the third column for the Series bubble size. In this way, we have added Series7 for Y-axis labels. Step-06: Adding New Labels➤ Click on the chart and then select the Chart Elements symbol. ➤ Check the Data Labels option. After that, all of the data labels will be visible on the chart. ➤ Select the labels of the X-axis and then Right-click here. ➤ Click on the Format Data Labels option. Afterward, the Format Data Labels pane will be visible on the right side. After that, the Data Label Range dialog box will open up. Then, you will return to the Format Data Labels part again. ➤ Select the Below option. In this way, we will be able to add our desired X-axis labels. ➤ Now, select the Y-axis labels and then Right-click here. ➤ Click on the Format Data Labels option. Afterward, the Format Data Labels pane will be visible on the right side. After that, the Data Label Range dialog box will open up. Then, you will be taken to the Format Data Labels part again. Finally, we will have the name of the products on the Y-axis labels. Step-07: Adding Labels for Bubbles➤ Select the bubbles with the number 5 and then Right-click on it. ➤ Choose the Format Data Labels option. After that, the Format Data Labels pane will open up in the right portion. After that, the labels of the bubbles will be converted into the values of the Selling Prices, Cost Prices, and Profits. ➤ You can remove the chart title by clicking on the Chart Elements symbol and then unchecking the Chart Title option. The final outlook of the chart will be like the following figure. Similar Readings
Type-02: Create a 4-Quadrant Matrix Chart in ExcelHere, we will be creating the other type of Matrix chart which is the 4-Quadrant Matrix chart. One thing is to remember that here you can only create a chart for 2 sets of values. So, we will use the selling prices and the cost prices of the 5 products to make a Quadrant chart. Step-01: Inserting Scattered Graph to Create a Matrix Chart in Excel➤ Select the range of values (C4:D8) and then go to the Insert Tab >> Charts Group >> Insert Scatter (X, Y) or Bubble Chart Dropdown >> Scatter Option. After that, the following graph will appear. Now, we have to set the upper bound and lower bound limits of the X-axis and
Y-axis. ➤ Choose the Format Axis option. Afterward, you will get the Format Axis pane on the right side. Then, we will have the modified X-axis labels with new limits and we don’t need to modify the Y-axis labels as the upper limit of this axis is here 3500 which is close to the maximum Cost Price of $3,197.00. Step-02: Creating Additional Data RangeFor adding the 2 lines to have 4 quadrants we have to add an additional data
range here. ➤ For the Horizontal part add the following values in the X
and Y coordinates. ➤ For the Vertical part add the following values in the X
and Y coordinates. Step-03: Addition of Four Points in Graph to Create Quadrant Lines➤ Select the graph, Right-click here, and then choose the Select Data option. Afterward, the Select Data Source wizard will open up. After that, the Edit Series dialog box will appear. Then the new series Series2 will be added and to insert a new series for the vertical line click on Add again. ➤
In the Edit Series dialog box, for Series X values select the X coordinates of the vertical part of the Quadrant sheet, and then for Series Y values select the Y coordinates of the vertical part. In this way, we have added the final series Series3 also, and then press OK. Finally, we will have 2 Orange points indicating the horizontal part and 2 Ash points indicating the vertical part. Step-04: Inserting Quadrant Lines to Create a Matrix Chart in Excel➤ Select the 2 Orange points and then Right-click here. ➤ Then choose the Format Data Series option. Afterward, you will have the Format Data Series pane on the right portion. ➤ To hide the points, go to the Fill & Line Tab >> expand the Marker Options Option >> click on the None option. In this way, the horizontal line will appear in the chart. Similarly, create the vertical separator line also by using the 2 ash points. Step-05: Inserting Data LabelsTo indicate the data points with the name of the products we have to add the data label first. ➤ Check the Data Labels option. After that, the values of the points will appear beside them and we have to convert them to the name of the products. ➤ Click on the Format Data Labels option. After that, you will have the Format Data Labels pane on the right side. Afterward, the Data Label Range dialog box will open up. ➤ Then uncheck the Y Value option and check the Left option as the Label Position. Finally, the outlook of the Quadrant Matrix Chart will be like the following. Practice SectionFor doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself. ConclusionIn this article, we tried to cover the steps to create a Matrix Chart in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section. For exploring more Excel-related articles you can visit our ExcelDemy site. Related Articles
Is there a matrix template in Excel?Excel Matrix Templates
You can extend the rows and columns of your data in an excel sheet without repeating the numbers. It will help you organize your work efficiently and complete it on time. You can also edit, customize, and use it in any way you want.
What is a basic matrix in Excel?A matrix is a type of visualization that is similar to a table in that it is made up of rows and columns. However, a matrix can be collapsed and expanded by rows and/or columns. If it contains a hierarchy, you can drill down/drill up. It can display totals and subtotals by columns and/or rows.
Is there a matrix function in Excel?Key to understanding the use of matrix operations in Excel is the concept of the Matrix (Array) formula. Such a formula uses matrix operations and returns a result that can be a matrix, a vector, or a scalar, depending on the computations involved.
How do you make a 3X3 matrix in Excel?Now you need to select 3X3 space in a spreadsheet; just enter the simple addition formula =A+B and then press Shift +Ctrl+Enter, and you'll have your addition of matrices (Note that the Braces will surround the formula).
|