Anzeige

The most important tips for Microsoft Excel 2016/2019

Wir haben mit Anwendern gesprochen die täglich mit Microsoft Excel 2016/2019 arbeiten, und daraus die häufigsten Fragen zusammengetragen.

Diese wertvollen Tipps die Ihnen viel Zeit und Nerven ersparen werden sollten Sie nicht verpassen.

Die wichtigsten Tipps für Microsoft Excel 2016/2019

Topic Overview

Anzeige

The most important tips for Microsoft Excel 2016/2019

Wir haben mit Anwendern gesprochen die täglich mit Microsoft Excel 2016/2019 arbeiten, und daraus die häufigsten Fragen zusammengetragen.

Diese wertvollen Tipps die Ihnen viel Zeit und Nerven ersparen werden sollten Sie nicht verpassen.

Die wichtigsten Tipps für Microsoft Excel 2016/2019

Topic Overview

Anzeige

1. Print out tables appropriately

1. Print out tables appropriately

A common problem when printing Excel tables is that only part of the required content is printed out. Fortunately, this can be fixed relatively easily.

Solution:

A great relief in the orientation of an Excel table is to make the page breaks visible

See fig. (Click to enlarge)

Begrenzungslinie in Excel anzeigen
Advertisement

You can also choose whether you want the worksheet in portrait or landscape format. The boundary lines then also change according to the selection.

See fig. (Click to enlarge)

Excel Seite ausrichten
Advertisement

In the standard setting, everything that goes beyond this margin can no longer be seen in the printed area and is printed on a second page.

  • So first of all we have to define the pressure range.
  • To do this, we mark the entire area of the table that is to be printed out.

See fig. (Click to enlarge)

Druckbereich in Excel festlegen

In the standard setting, everything that goes beyond this margin can no longer be seen in the printed area and is printed on a second page.

  • So first of all we have to define the pressure range.
  • To do this, we mark the entire area of the table that is to be printed out.

See fig. (Click to enlarge)

 Druckausgabe in Excel anpassen
Advertisement

A common problem when printing Excel tables is that only part of the required content is printed out. Fortunately, this can be fixed relatively easily.

Solution:

A great relief in the orientation of an Excel table is to make the page breaks visible

See fig. (Click to enlarge)

Begrenzungslinie in Excel anzeigen
Advertisement

You can also choose whether you want the worksheet in portrait or landscape format. The boundary lines then also change according to the selection.

See fig. (Click to enlarge)

Excel Seite ausrichten
Advertisement

In the standard setting, everything that goes beyond this margin can no longer be seen in the printed area and is printed on a second page.

  • So first of all we have to define the pressure range.
  • To do this, we mark the entire area of the table that is to be printed out.

See fig. (Click to enlarge)

Druckbereich in Excel festlegen

In the standard setting, everything that goes beyond this margin can no longer be seen in the printed area and is printed on a second page.

  • So first of all we have to define the pressure range.
  • To do this, we mark the entire area of the table that is to be printed out.

See fig. (Click to enlarge)

 Druckausgabe in Excel anpassen
Advertisement

2. Line break within a cell

2. Line break within a cell

If you often create Excel tables (e.g. invoice templates) in which there is also a lot of text, you will at some point run into the problem that if the cells are automatically wrapped, the text is wrapped exactly where you can’t use it at all.

Solution:

  • You can also edit all the text you enter in a cell in the formula bar.
  • First mark the cell in which the text to be edited is located.
  • Next, expand the field of view of the formula bar.

See fig. (Click to enlarge)

Bearbeitungsleiste in Excel anpassen

Now place the cursor directly in front of the first letter of the word which is to be broken into the next line. (In our example “in”)

See fig. (Click to enlarge)

Excel Cursor positionieren
Advertisement
  • Now first press the “ALT” key and hold it down, and then the “ENTER” key.

Note:
You can break the text as often as you want and at any cell you want. However, if you later change the width and / or height of the cell, this can lead to undesirable results. You should only make these optical adjustments when the table is basically finished in terms of content.

If you often create Excel tables (e.g. invoice templates) in which there is also a lot of text, you will at some point run into the problem that if the cells are automatically wrapped, the text is wrapped exactly where you can’t use it at all.

Solution:

  • You can also edit all the text you enter in a cell in the formula bar.
  • First mark the cell in which the text to be edited is located.
  • Next, expand the field of view of the formula bar.

See fig. (Click to enlarge)

Bearbeitungsleiste in Excel anpassen

Now place the cursor directly in front of the first letter of the word which is to be broken into the next line. (In our example “in”)

See fig. (Click to enlarge)

Excel Cursor positionieren
Advertisement
  • Now first press the “ALT” key and hold it down, and then the “ENTER” key.

Note:
You can break the text as often as you want and at any cell you want. However, if you later change the width and / or height of the cell, this can lead to undesirable results. You should only make these optical adjustments when the table is basically finished in terms of content.

3. Save table as PDF

3. Save table as PDF

If you have created an Excel table and would like to send the calculated results (e.g. by e-mail) to someone who may not be using Excel or an incompatible version for viewing purposes only. it can be helpful to convert these into the familiar PDF format beforehand.

Solution:
After you have created your table, do not just click save, as Excel usually uses the standard “xlxs” file format, but click “Save as” and then select the “PDF” file format

Tip:
The easiest way to get to the save options is to press the “F12” key directly from the worksheet.

If you have created an Excel table and would like to send the calculated results (e.g. by e-mail) to someone who may not be using Excel or an incompatible version for viewing purposes only. it can be helpful to convert these into the familiar PDF format beforehand.

Solution:
After you have created your table, do not just click save, as Excel usually uses the standard “xlxs” file format, but click “Save as” and then select the “PDF” file format

Tip:
The easiest way to get to the save options is to press the “F12” key directly from the worksheet.

4. Protect the table with a password

4. Protect the table with a password

If you have created an Excel spreadsheet, it is often saved on a drive (e.g. in a network) to which many people within this network have access. However, not everyone should have access to all files in this drive.

It can therefore be helpful to protect certain files from unauthorized access with a password.

Solution:

After you have created your tables in a workbook, go to: “File / Information / Protect workbook” to the point: “Encrypt with password”

See fig. (Click to enlarge)

Excel Arbeitsmappe verschlüsseln
Nord VPN
Anzeige

Here you can now assign any password that will be required later to open the file.

Please make a careful note of the password, otherwise you will no longer be able to open the file without this password!

See fig. (Click to enlarge)

Passwort in Excel vergeben
Advertisement

Note:
The file is still visible to everyone, but the workbook can only be opened with the valid password (case-sensitive).

If you have created an Excel spreadsheet, it is often saved on a drive (e.g. in a network) to which many people within this network have access. However, not everyone should have access to all files in this drive.

It can therefore be helpful to protect certain files from unauthorized access with a password.

Solution:

After you have created your tables in a workbook, go to: “File / Information / Protect workbook” to the point: “Encrypt with password”

See fig. (Click to enlarge)

Excel Arbeitsmappe verschlüsseln
Nord VPN
Anzeige

Here you can now assign any password that will be required later to open the file.

Please make a careful note of the password, otherwise you will no longer be able to open the file without this password!

See fig. (Click to enlarge)

Passwort in Excel vergeben
Advertisement

Note:
The file is still visible to everyone, but the workbook can only be opened with the valid password (case-sensitive).

5. Mathematical signs in cells

5. Mathematical signs in cells

Since Excel is a spreadsheet and not a word processing program, it is a bit difficult for some entries that have to do with text to implement them as the user would like.

Example:
You want to enter the following text in a cell: “= the value of:”

The problem that will now occur after you have entered the equal sign and your text and confirmed with Enter is that Excel assumes that you want to enter a formula, since all formulas in Excel now begin with an “=” sign. The following error message then appears: “#Name?”

Solution:
In the cell in which you want to start with the “=” sign, insert an invisible apostrophe beforehand with the key combination “ALT GR” + “apostrophe (´)”. This is not displayed and also not printed.

Note:
This procedure can be applied to all mathematical signs such as +, -, / etc.

Since Excel is a spreadsheet and not a word processing program, it is a bit difficult for some entries that have to do with text to implement them as the user would like.

Example:
You want to enter the following text in a cell: “= the value of:”

The problem that will now occur after you have entered the equal sign and your text and confirmed with Enter is that Excel assumes that you want to enter a formula, since all formulas in Excel now begin with an “=” sign. The following error message then appears: “#Name?”

Solution:
In the cell in which you want to start with the “=” sign, insert an invisible apostrophe beforehand with the key combination “ALT GR” + “apostrophe (´)”. This is not displayed and also not printed.

Note:
This procedure can be applied to all mathematical signs such as +, -, / etc.

6. Display formulas

6. Display formulas

Anyone who has ever created an extensive Excel table with a lot of calculations knows that at some point there will come a point where you no longer know exactly in which cells calculations were made and where numbers were entered manually.

Wouldn’t it be helpful to know in which cells there are formulas?

Solution:
Select under the register: “Formulas” / “Show formulas”

See fig. (Click to enlarge)

Formeln in Excel anzeigen

Here you can now see exactly where formulas are stored in your worksheet.

Anyone who has ever created an extensive Excel table with a lot of calculations knows that at some point there will come a point where you no longer know exactly in which cells calculations were made and where numbers were entered manually.

Wouldn’t it be helpful to know in which cells there are formulas?

Solution:
Select under the register: “Formulas” / “Show formulas”

See fig. (Click to enlarge)

Formeln in Excel anzeigen

Here you can now see exactly where formulas are stored in your worksheet.

7. Display the calculation path

7. Display the calculation path

When you have created a table with some calculations and cell references, you can, as already shown in tip 6, display the cells where formulas are stored.

However, the longer a formula is, the more strenuous it is to look for the cells specified there on the sheet and to find an error. Wouldn’t it be practical if the trace that led to the calculation could be displayed?

Solution:
Select a cell in which Excel calculations were carried out, and then select in the register: “Formulas” / “Track to predecessor”

See fig. (Click to enlarge)

Spur zum Vorgänger in Excel

Here the arrows show you with which cell values the result in the marked cell came about.

Note:
If too many cell references have flowed into the result of a cell, it will of course become confusing again with all the arrows, but this tip can be quite helpful for smaller calculations.

When you have created a table with some calculations and cell references, you can, as already shown in tip 6, display the cells where formulas are stored.

However, the longer a formula is, the more strenuous it is to look for the cells specified there on the sheet and to find an error. Wouldn’t it be practical if the trace that led to the calculation could be displayed?

Solution:
Select a cell in which Excel calculations were carried out, and then select in the register: “Formulas” / “Track to predecessor”

See fig. (Click to enlarge)

Spur zum Vorgänger in Excel

Here the arrows show you with which cell values the result in the marked cell came about.

Note:
If too many cell references have flowed into the result of a cell, it will of course become confusing again with all the arrows, but this tip can be quite helpful for smaller calculations.

8. Write protection for individual cells

8. Write protection for individual cells

If you have created a table and you share it with others (e.g. in a network) and you should only be able to edit certain areas, but the rest of the table can only be changed by yourself, you should write-protect these cells .

Solution:
Basically, all cells in Excel are marked with “Locked” in the formatting. This means that if you now write-protect the worksheet, nothing can be changed at all.
First, mark all cells or cell areas that should later be able to be changed by all employees who have access to the file.
Then activate the context menu over the marked cells with the right mouse button and select “Format cells”.

See fig. (Click to enlarge)

Zellen in Excel formatieren
Advertisement

Next, go to the “Protection” tab in the cell formatting menu, and remove the tick “Locked”.
In the last step, we go to “Protect sheet” under the “Check” tab and assign a password there (optionally), and check the respective checkmarks for the processing restrictions that we would like to assign to these cells.

See fig. (Click to enlarge)

Excel Arbeitsblatt schützen
Advertisement

If you have created a table and you share it with others (e.g. in a network) and you should only be able to edit certain areas, but the rest of the table can only be changed by yourself, you should write-protect these cells .

Solution:
Basically, all cells in Excel are marked with “Locked” in the formatting. This means that if you now write-protect the worksheet, nothing can be changed at all.
First, mark all cells or cell areas that should later be able to be changed by all employees who have access to the file.
Then activate the context menu over the marked cells with the right mouse button and select “Format cells”.

See fig. (Click to enlarge)

Zellen in Excel formatieren
Advertisement

Next, go to the “Protection” tab in the cell formatting menu, and remove the tick “Locked”.
In the last step, we go to “Protect sheet” under the “Check” tab and assign a password there (optionally), and check the respective checkmarks for the processing restrictions that we would like to assign to these cells.

See fig. (Click to enlarge)

Excel Arbeitsblatt schützen
Advertisement

9. The error correction in Excel

9. The error correction in Excel

Popular saying goes that if you don’t make mistakes, you don’t work either. And there is actually something to this wisdom. Because it just happens that you just “clicked” yourself at a certain point, and suddenly everything looks completely different than it did a second ago. Fortunately, Excel 2016 allows you to undo up to 100 work steps.

Solution:
In the upper area of the worksheet (directly above the “Start” tab) go to the small arrow pointing downwards, and you can see the last work steps made there.

Now select from the list of the last work steps displayed how many steps you want to undo.

See fig. (Click to enlarge)

Arbeitsschritte in Excel rückgängig

Note:
In most cases, the mistake is immediately identified and you want to reverse it quickly. In this case, just use the following keyboard shortcuts:

  • “CTRL” + “Z” (undo last step)
  • “CTRL” + “Y” (one more step forward)

Popular saying goes that if you don’t make mistakes, you don’t work either. And there is actually something to this wisdom. Because it just happens that you just “clicked” yourself at a certain point, and suddenly everything looks completely different than it did a second ago. Fortunately, Excel 2016 allows you to undo up to 100 work steps.

Solution:
In the upper area of the worksheet (directly above the “Start” tab) go to the small arrow pointing downwards, and you can see the last work steps made there.

Now select from the list of the last work steps displayed how many steps you want to undo.

See fig. (Click to enlarge)

Arbeitsschritte in Excel rückgängig

Note:
In most cases, the mistake is immediately identified and you want to reverse it quickly. In this case, just use the following keyboard shortcuts:

  • “CTRL” + “Z” (undo last step)
  • “CTRL” + “Y” (one more step forward)

10. Freeze the view in Excel

10. Freeze the view in Excel

Especially with somewhat longer tables, it makes perfect sense to fix the top line of a table so that it remains constantly visible.

You can do this very simply by selecting “Fix top line” in the “View” tab – “Fix window”.
But then only the top line is fixed and remains permanently visible.

Another variant that can sometimes be useful is that an entire area remains permanently displayed. But you can basically just as easily reach this via the above-mentioned path. The only difference is that instead of “Fix top line” simply “Fix window” is selected.

The position of the cursor (the currently selected cell) is decisive for fixing a window. The window is fixed from exactly this cell to the left and upwards.

See fig .: (click to enlarge)

obere Zeile in Excel fixieren
Fenster in Excel fixieren
Blogverzeichnis Bloggerei.de

Especially with somewhat longer tables, it makes perfect sense to fix the top line of a table so that it remains constantly visible.

You can do this very simply by selecting “Fix top line” in the “View” tab – “Fix window”.
But then only the top line is fixed and remains permanently visible.

Another variant that can sometimes be useful is that an entire area remains permanently displayed. But you can basically just as easily reach this via the above-mentioned path. The only difference is that instead of “Fix top line” simply “Fix window” is selected.

The position of the cursor (the currently selected cell) is decisive for fixing a window. The window is fixed from exactly this cell to the left and upwards.

See fig .: (click to enlarge)

obere Zeile in Excel fixieren
Fenster in Excel fixieren
Blogverzeichnis Bloggerei.de

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Popular Posts:

305, 2022

The Xreference function – difference to Sreference and Wreference

May 3rd, 2022|Categories: Microsoft Excel, Microsoft Office, Uncategorized|Tags: , |

Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen

Popular Posts:

305, 2022

The Xreference function – difference to Sreference and Wreference

May 3rd, 2022|Categories: Microsoft Excel, Microsoft Office, Uncategorized|Tags: , |

Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen
2021-11-11T08:39:17+01:00By |Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , |

Title

Anzeige

Most read:

Search by category:

Go to Top