Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

The most important tips for Excel

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 Excel

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
cshow
Ads

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
cshow
Ads

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
cshow
Ads

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
cshow
Ads

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
  • 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
  • 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

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
cshow
Ads

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

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
cshow
Ads

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

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
cshow
Ads

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

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
cshow
Ads

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 for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones
2023-06-03T09:51:00+02:00By |Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: |
Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top