Contains paid promotion

Anzeige

How to create individual diagrams
in Excel 2016

As important as numbers are for decision-making and comparability, there’s nothing like a chart to show trends and trends as closely as possible.

Since Excel 2016 there are a lot of simplifications for us users, but there are always certain questions on how to customize and design the diagram.

Create individual charts in Excel 2016
Anzeige
SanDisk Ultra 200GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1
SanDisk Ultra 200GB microSDXC Speicherkarte + Adap...*
by SanDisk

Kapazität: 200 GB, Flash Card Typ: MicroSDXC, Flash-Memory-...

List Price: € 82.99 You Save: € 33.99 (41%) Prime Price: € 49.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 25. September 2018 um 21:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

How to create individual diagrams in Excel 2016

As important as numbers are for decision-making and comparability, there’s nothing like a chart to show trends and trends as closely as possible.

Since Excel 2016 there are a lot of simplifications for us users, but there are always certain questions on how to customize and design the diagram.

Create individual charts in Excel 2016
Anzeige
SanDisk Ultra 200GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1
SanDisk Ultra 200GB microSDXC Speicherkarte + Adap...*
by SanDisk

Kapazität: 200 GB, Flash Card Typ: MicroSDXC, Flash-Memory-...

List Price: € 82.99 You Save: € 33.99 (41%) Prime Price: € 49.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 25. September 2018 um 21:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Set the data source

1. Set the data source

Before we can even begin to create a diagram, of course we need a data source from which the diagram is to be formed.

In our example, we have created a small table in which we have shown on the vertical axis 1 year (January – December), and on the horizontal axis fictional casinos with their monthly income.

In order to create a diagram from the available data, we proceed as follows:

  • Mark the entire table
  • Under the “Insert” tab, click on the small arrow on the right in the “Diagrams” column to display all available diagram types

Note:
If you have a large table, you can mark it most quickly by placing it in the cursor in the 1st cell (top left), and then holding down the “CTRL + SHIFT” key and then using the arrow keys on the keyboard 1x to the right , and then press down 1x.

This will only mark the area of ​​a worksheet filled with content (not broken by blank lines).

See picture (click to enlarge)

1. Select Table and select Chart Type
Advertisement

Here you can now choose from the available diagram types which are best suited for your presentation.

In our example, we opted for a classic bar chart. In retrospect, however, that can be changed again at any time.

If you want to zoom in on the chart without having to scroll right and left with your mouse, you can easily move it to a new spreadsheet.

Note:
Of course, the data source will remain untouched, and it will not be a new spreadsheet, just an additional spreadsheet within a folder.

See picture: (click to enlarge)

2. Move diagram to worksheet
Anzeige

Before we can even begin to create a diagram, of course we need a data source from which the diagram is to be formed.

In our example, we have created a small table in which we have shown on the vertical axis 1 year (January – December), and on the horizontal axis fictional casinos with their monthly income.

In order to create a diagram from the available data, we proceed as follows:

  • Mark the entire table
  • Under the “Insert” tab, click on the small arrow on the right in the “Diagrams” column to display all available diagram types

Note:
If you have a large table, you can mark it most quickly by placing it in the cursor in the 1st cell (top left), and then holding down the “CTRL + SHIFT” key and then using the arrow keys on the keyboard 1x to the right , and then press down 1x.

This will only mark the area of ​​a worksheet filled with content (not broken by blank lines).

See picture:

1. Select Table and select Chart Type
Advertisement

Here you can now choose from the available diagram types which are best suited for your presentation.

In our example, we opted for a classic bar chart. In retrospect, however, that can be changed again at any time.

If you want to zoom in on the chart without having to scroll right and left with your mouse, you can easily move it to a new spreadsheet.

Note:
Of course, the data source will remain untouched, and it will not be a new spreadsheet, just an additional spreadsheet within a folder.

See picture:

2. Move diagram to worksheet
Anzeige

2. Limiting the data source

2. Limiting the data source

Just as our diagram looks like it may still go that way, but you can see that the more column values on the X-axis have to be displayed at the same time, the more confusing it will be that too many colors are actually displayed.

But since Excel 2016 it has become much easier to select specific values for a comparison. So we can instead of 6 casinos, e.g. compare only 3 with each other.

  • To do this, we click once in an empty area of the diagram
  • Select the filter icon from the context menu (to the right)
  • Remove the hooks from the unneeded data columns
  • Confirm with“Apply”

See picture: (click to enlarge)

3. Limit the data source
4. Bar chart

This makes the matter much clearer, but on the other hand of course also more patchy. For now we are missing the 3 remaining casinos here. Here you can either do this by simply creating a second graph, or you can use the dynamic possibilities depending on which data should be compared.

Also, you can just try another type of chart (such as a line or pie chart).

Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

List Price: € 229.95 You Save: € 20.05 (9%) Prime Price: € 209.90 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 25. September 2018 um 18:21 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Just as our diagram looks like it may still go that way, but you can see that the more column values on the X-axis have to be displayed at the same time, the more confusing it will be that too many colors are actually displayed.

But since Excel 2016 it has become much easier to select specific values for a comparison. So we can instead of 6 casinos, e.g. compare only 3 with each other.

  • To do this, we click once in an empty area of the diagram
  • Select the filter icon from the context menu (to the right)
  • Remove the hooks from the unneeded data columns
  • Confirm with“Apply”

See picture:

3. Limit the data source
4. Bar chart

This makes the matter much clearer, but on the other hand of course also more patchy. For now we are missing the 3 remaining casinos here. Here you can either do this by simply creating a second graph, or you can use the dynamic possibilities depending on which data should be compared.

Also, you can just try another type of chart (such as a line or pie chart).

Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

List Price: € 229.95 You Save: € 20.05 (9%) Prime Price: € 209.90 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 25. September 2018 um 18:21 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

3. Adjust axis and data labels

3. Adjust axis and data labels

Now we can customize the captions for the axes, the chart title and the data series in various ways.

To do this, we mark the part of the diagram we want to change, and then select data series or axis formatting from the context menu.

Also, here we can add or remove additional elements to our diagram, e.g. a data table, which offers itself here better than about a data directly above the data bar, because they would overlap again.

See picture: (click to enlarge)

5. Adjust the axis labels in the diagram
6. Insert data table in diagram

Of course, every diagram can always be adjusted afterwards in all areas. Just select the appropriate area and use the context menu to select the desired customization.

Advertisement
#1
WD Elements Portable, Externe Festplatte - 1 TB - USB 3.0 - WDBUZG0010BBK-WESN
WD Elements Portable, Externe Festplatte - 1 TB - ...*
by Western Digital

Festplattenkapazität: 1000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 25. September 2018 um 17:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Now we can customize the captions for the axes, the chart title and the data series in various ways.

To do this, we mark the part of the diagram we want to change, and then select data series or axis formatting from the context menu.

Also, here we can add or remove additional elements to our diagram, e.g. a data table, which offers itself here better than about a data directly above the data bar, because they would overlap again.

See picture:

5. Adjust the axis labels in the diagram
6. Insert data table in diagram

Of course, every diagram can always be adjusted afterwards in all areas. Just select the appropriate area and use the context menu to select the desired customization.

Advertisement
#1
WD Elements Portable, Externe Festplatte - 1 TB - USB 3.0 - WDBUZG0010BBK-WESN
WD Elements Portable, Externe Festplatte - 1 TB - ...*
by Western Digital

Festplattenkapazität: 1000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 25. September 2018 um 17:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

4. Swap X-axis and Y-axis (transpose)

4. Swap X-axis and Y-axis (transpose)

If you want to swap the X-axis (horizontal) and the Y-axis (vertical), there are 2 possibilities.

The first (and simplest) is simply to click the diagram in an empty area to make it complete, and then click on “Toggle Line / Column” in the “Design” tab.

See picture: (click to enlarge)

7. Swap axes in diagram (transpose)
Advertisement

The second option (a bit more complex) is to swap (transpose) the axes of the data source itself.

  • To do this we go into our original table and mark it completely
  • Then press“CTRL + C”
  • Click in an empty area of the worksheet
  • Select “Transpose” in the context menu under the paste options

After that, you will of course have to change the data source in the chart by clicking again in an empty area of the chart and clicking on “Select data” in the context menu.

The result is then the same as described above for the axis inversion of the diagram.

See picture: (click to enlarge)

8. Transpose Excel spreadsheet

As you can see, it makes absolutely no sense to transpose the whole thing in our diagram, but it does not hurt if you know how it works, because at some point the need might be there.

If you want to swap the X-axis (horizontal) and the Y-axis (vertical), there are 2 possibilities.

The first (and simplest) is simply to click the diagram in an empty area to make it complete, and then click on “Toggle Line / Column” in the “Design” tab.

See picture:

7. Swap axes in diagram (transpose)
Advertisement

The second option (a bit more complex) is to swap (transpose) the axes of the data source itself.

  • To do this we go into our original table and mark it completely
  • Then press“CTRL + C”
  • Click in an empty area of the worksheet
  • Select “Transpose” in the context menu under the paste options

After that, you will of course have to change the data source in the chart by clicking again in an empty area of the chart and clicking on “Select data” in the context menu.

The result is then the same as described above for the axis inversion of the diagram.

See picture:

8. Transpose Excel spreadsheet

As you can see, it makes absolutely no sense to transpose the whole thing in our diagram, but it does not hurt if you know how it works, because at some point the need might be there.

Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
*Zuletzt aktualisiert am 25. September 2018 um 21:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
*Zuletzt aktualisiert am 25. September 2018 um 21:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-14T11:16:20+00:00By |Categories: Excel 2016, Office 2016|Tags: , , , , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok