how to subtract using relative cell reference excel 2013

The second data set will be arranged in the second row of the table and the active cell will be B6. You need to run the macro repeatedly to complete the data arrangement in the table as given below. Your macro for arranging the data is ready. You can see that the active cell is B5 and the next data set will be placed here.

Delete the rows B6 – B11 and click in the cell B5. The first data set is arranged in the first row of the table. is the current row number – the 3 rows above it.Ĭut the cells B5, B6, B7, B8 and B9 and paste it in the cells C4 to C8 respectively. Give a meaningful name, say, DataArrange to the macro. The first step in arranging the above given data is to define the data format in a table with headers.Ĭreate the row of headers as shown below. In order to let the macro recorder know that it has to use relative references, do the following − The macro needs to use relative references, as you will move down the rows while arranging the data. The solution is to record a macro so that you can complete the task in not more than a few seconds. It becomes a mundane task not allowing you to focus on technical things It takes substantial amount of time to arrange the data from the 280 constituencies If you attempt to arrange the given data in the above format − Therefore, arrange the data in a table as shown below. It is not possible to analyze the data in the above format. The data is provided to you in a worksheet as given below. For each constituency, the following details are collected − Suppose you are required to analyze the data of voters collected from 280 constituencies. Such macros will be useful if you have to repeat the steps at various places in the worksheet. Another example is SUM(A1, A3, A5) which adds the numbers that are contained in cells A1, A3, and A5 (A1, A3, and A5 are arguments).Relative reference macros record an offset from the active cell. For example, SUM(A1:A5) adds all the numbers in the range of cells A1 through A5.

Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. The SUM function adds all the numbers that you specify as arguments. To switch between viewing the results and viewing the formulas, press CTRL+` (grave accent) on your keyboard.Or, click the Show Formulas button (on the Formulas tab). In the worksheet, select cell A1, and then press CTRL+V. Subtracts 9000 from 15000 (which equals 6000)Īdds all number in the list, including negative numbers (net result is 16000) Select all of the rows in the table below, then press CTRL-C on your keyboard. Exampleįollow these steps to subtract numbers in different ways: Use the SUM function and convert any numbers that you want to subtract to their negative values. Note: There is no SUBTRACT function in Excel.

How to subtract using relative cell reference excel 2013