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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
- 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)
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)
- 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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
Popular Posts:
Integrate and use ChatGPT in Excel – is that possible?
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.
Create Out of Office Notice in Outlook
To create an Out of Office message in Microsoft Outlook - Office 365, and start relaxing on vacation
The best backup solutions for your data
Keep your data safe and secure! Discover our best backup solutions for your valuable information now. Because safety is the be-all and end-all - and we have the perfect tips.
Internet Addiction – A serious look at a growing problem
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.
Main keyboard shortcuts in Windows 10/11
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.
Encrypt USB stick – These options are available
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.
Popular Posts:
Integrate and use ChatGPT in Excel – is that possible?
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.
Create Out of Office Notice in Outlook
To create an Out of Office message in Microsoft Outlook - Office 365, and start relaxing on vacation
The best backup solutions for your data
Keep your data safe and secure! Discover our best backup solutions for your valuable information now. Because safety is the be-all and end-all - and we have the perfect tips.
Internet Addiction – A serious look at a growing problem
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.
Main keyboard shortcuts in Windows 10/11
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.
Encrypt USB stick – These options are available
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.