Wednesday, January 23, 2019

Bar Chart

A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels.

To create a bar chart, execute the following steps.


  • Select the range A1: B6.

Select data range for bar chart

  • On the Insert tab, in the Charts group, click the Column symbol.

insert chart bar

  • Click Clustered Bar. 
    click the clustered bar
  • Result: 
result bar chart




Column Chart

Column charts are used to compare values across categories by using vertical bars.

To create a column chart, execute the following steps.


  • Select the range A1: A7, hold down CTRL and select the range C1: D7.

select-data column chart

  • On the Insert tab, in the Charts group, click the Column symbol.
insert column chart

  • Click Clustered Column.
    click-clustered-column
  • Result: 
    result column chart

Note: only if you have numeric labels, empty cell A1 before you create the column chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.




Line Chart

Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis. Use a scatter chart (XY chart) to show scientific XY data.

To create a line chart, execute the following steps.


  • Select the range A1: D7. 
data range

  • On the Insert tab, in the Charts group, click the Line symbol.
insert line chart

  • Click Line with Markers.
choose line with markers

  • Result: 
    result line chart


Note: Only if you have numeric labels, empty cell A1 before you create the line chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.





Pie Chart

Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series.

To create a pie chart of the 2017 data series, execute the following steps.

Select the range A1: D2.

select data

On the Insert tab, in the Charts group, click the Pie symbol.

insert pie chart


Click Pie.
choose pie chart

The result after choose pie.

normal pie chart

Click on the pie to select the whole pie. Click on a slice to drag it away from the center.
The result after.

exploded chart

Note: Only if you have numeric labels, empty cell A1 before you create the pie chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically creates the correct chart. After creating the chart, you can enter the text Year into cell A1 if you like.


Tuesday, January 22, 2019

Percentage

Calculating percentages in Excel is easy. Percentage simply means 'out of 100', so 72% is '72 out of 100' and 4% is '4 out of 100', etc.

Enter a Percentage


To enter a percentage in Excel, execute the following steps.


First, enter a decimal number.

enter decimal number

On the Home tab, in the Number group, click the percentage symbol to apply a Percentage format.

percentage format

Here the result.

result percentage

Note: To change the percentage in cell A1, simply select cell A1 and type a new percentage (do not type a decimal number). 

Percentage of Total


To calculate the percentage of a total in Excel, execute the following steps.

First, enter the formula is shown below. This formula divides the value in cell A1 by the value in cell B1. Simply use the forward slash (/) as the division operator. Don't forget, always start a formula with an equal sign (=).

division formula


On the Home tab, in the Number group, click the percentage symbol to apply a Percentage format.
Here the result.

result percentage of total


On the Home tab, in the Number group, click the Increase Decimal button once.

increase decimal

Here the result of percentage with one decimal place.

result percentage with one decimal place


Note: Excel always uses the underlying precise value in calculations, regardless of how many decimals you choose to display.


Increase by Percentage


To increase a number by a percentage in Excel, execute the following steps.

First, enter a number in cell A1. Enter a decimal number (0.2) in cell B1 and apply a Percentage format.

increase a number by a percentage

To increase the number in cell A1 by 20%, multiply the number by 1.2 (1+0.2). The formula below does the trick.

increase number


To decrease a number by a percentage, simply change the plus sign to a minus sign.

decrease number by a percentage

Note: Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that part will be calculated first.


Percentage Change


To calculate the percentage change between two numbers in Excel, execute the following steps.

Enter an old number in cell A1 and a new number in cell B1.

enter old number


First, calculate the difference between new and old.

calculate the difference between new and old


Next, divide this result by the old number in cell A1.

divide result by the old number in cell


On the Home tab, in the Number group, click the percentage symbol to apply a Percentage format.
Here the result.

percentage-change result


The (New-Old)/Old formula always works.

new to old number


Note: Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that part will be calculated first.


Multiply

To multiply numbers in Excel, use the asterisk symbol (*) or the PRODUCT function. Learn how to multiply columns and how to multiply a column by a constant.


  • 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 (=).

multiply in a cell

  • The formula below multiplies the values in cells A1, A2 and A3.

multiply numbers in a range

  • As you can imagine, this formula can get quite long. Use the PRODUCT function to shorten your formula. For example, the PRODUCT function below multiplies the values in the range A1: A7.
function multiply

  • Here's another example.
    more advance function
Explanation: =A1*A2*A3*A4*A5*A6*A7*B1*B2*B3*B4*C1*8 produces the exact same result.


 To multiply two columns, execute the following steps.


First, multiply the value in cell A1 by the value in cell B1.

relative reference multiply value two column


Next, select cell C1, click on the lower right corner of cell C1 and drag it down to cell C6.

Multiply by column


To multiply a column of numbers by a constant number, execute the following steps.


First, multiply the value in cell A1 by the value in cell A8. Fix the reference to cell A8 by placing a $ symbol in front of the column letter and row number ($A$8).
multiply absolute reference

 Next, select cell B1, click on the lower right corner of cell B1 and drag it down to cell B6.

multiply a column contain number

Explanation: when we drag the formula down, the absolute reference ($A$8) stays the same, while the relative reference (A1) changes to A2, A3, A4, etc. You can also use Paste Special to quickly multiply a range of cells by a constant number.



Enter a formula

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.

To enter a formula, execute the following steps.

  1. Select a cell. 
  2. To let Excel know that you want to enter a formula, type an equal sign (=). 
  3. For example, type the formula A1+A2.  
    formula example
  4. Change the value of cell A1 to 3.
    recalculation
Tip: instead of typing A1 and A2, simply select cell A1 and cell A2.

Friday, January 18, 2019

Subtract

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?

  • 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 (=).

substract 1


  • The formula below subtracts the value in cell A2 and the value in cell A3 from the value in cell A1. 
    subtract-numbers-in-a-range

  • As you can imagine, this formula can get quite long. Simply use the SUM function to shorten your formula. For example, the formula below subtracts the values in the range A2: A9 from the value in cell A1.
    use-the-sum-function-to-subtract-numbers


To subtract the numbers in column B from the numbers in column A, execute the following steps.


  • First, subtract the value in cell B1 from the value in cell A1.
    substract value from cell
  • Next, select cell C1, click on the lower right corner of cell C1 and drag it down to cell C6.
    select & drag cell


To subtract a number from a range of cells, execute the following steps. 


  1. First, subtract the value in cell A8 from the value in cell A1. Fix the reference to cell A8 by placing a $ symbol in front of the column letter and row number ($A$8).
    subtract a number from a range of cells
  2. Next, select cell B1, click on the lower right corner of cell B1 and drag it down to cell B6.
    subtract a number from a range of cells_2

When we drag the formula down, the absolute reference ($A$8) stays the same, while the relative reference (A1) changes to A2, A3, A4, etc. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful features Excel has to offer.

Thursday, January 17, 2019