This chapter shows you one of the many powerful features Excel has to offer. The example below illustrates how to create a budget in Excel. 1. Enter the different type of income and expenses in column A. Note: On the Home tab, in the Font group, you can use the different commands to change the font size and font …
Normally, when you copy a range of data cells into a new range that contains data already, it overwrites the new range. But, what if you want to add content from new range excluding blanks to the old cell range. Use the ‘Paste Special Skip Blanks’ option and Excel will not overwrite existing values with blanks. 1. Select the range B1:B12. 2. Right …
There’s no SUBTRACT function in Excel. However, there are several ways to subtract numbers in Excel. Are you ready to improve your Excel skills? 1. For example, the formula below subtracts numbers in a cell. Simply use the minus sign (-). Don’t forget, always start a formula with an equal sign (=). 2. The formula below subtracts the …
This example illustrates the various paste options (Paste, Values, Formulas, Formatting, Paste Special) in Excel . Cell B5 below contains the SUM function which calculates the sum of the range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders. Paste The Paste option pastes everything. 1. Select cell B5, right click, …
A dynamic named range expands automatically when you add a value to the range. 1. For example, select the range A1:A4 and name it Prices. 2. Calculate the sum. 3. When you add a value to the range, Excel does not update the sum. To expand the named range automatically when you add a value to the range, execute the following …
The square root of a number is a value that, when multiplied by itself, gives the number. The SQRT function in Excel returns the square root of a number. 1. First, to square a number, multiply the number by itself. For example, 4 * 4 = 16 or 4^2 = 16. Note: to insert a caret ^ symbol, press SHIFT + …
Percentage simply means ‘out of 100′, so 72% is ’72 out of 100’ and 4% is ‘4 out of 100’, etc. Enter a Percentage Percentage of Total Increase by Percentage Percentage Change Calculating percentages in Excel is easy. Enter a Percentage To enter a percentage in Excel, execute the following steps. 1. First, enter a decimal number. …
Learn how to multiply columns and how to multiply a column by a constant. To multiply numbers in Excel, use the asterisk symbol (*) or the PRODUCT function. 1. The formula below multiplies numbers in a cell. Simply use the asterisk symbol (*) as the multiplication operator. Don’t forget, always start a formula with an equal sign …
Paste Special Transpose Transpose Function Use the ‘Paste Special Transpose’ option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function. Paste Special Transpose To transpose data, execute the following steps. 1. Select the range A1:C1. 2. Right click, and then click Copy. 3. Select cell E2. 4. Right …
By using names you can make your formulas much easier to understand and maintain i.e to update, audit, and manage. You can define a name for a cell range, function, constant, or table. Name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value. Named Range …
The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change. 1a. Select cell C3 and enter the formula shown below. 1b. Select cell C3. On the Home tab, in the Number group, apply a Percentage format. 1c. Select cell C3, click on the lower right corner of cell …
Cell references in Excel are very important. Building a structure or a template in excel using formula one needs to understand the difference between relative, absolute and mixed reference. Relative Reference To identify relative referencing, it is simply the ‘cell name’ as illustrated below. N/B: Cell Name comprises of a column label and a row number of …
Every formula in Excel starts with an equal sign, function name then in bracket a cell name or cell ranges or cell references. See illustration below: =Function Name( cell name or cell ranges or cell references) Example: = SUM(A1:A4) A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. …