Contains paid promotion

Advertisement

Excel 2016 Most Wanted –
The best tips & tricks

Die besten Tipps für Excel 2016

We talked to users who work with Excel 2016 on a daily basis and collected the most common questions.

You should not miss these valuable tips that will save you a lot of time and nerves

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

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

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Print out Excel spreadsheets.

Excel 2016 Most Wanted – The best tips & tricks

Die besten Tipps für Excel 2016

We talked to users who work with Excel 2016 on a daily basis and collected the most common questions.

You should not miss these valuable tips that will save you a lot of time and nerves

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

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

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Print out Excel spreadsheets.

A common problem with printing Excel spreadsheets is that only part of the required content is printed out.

Fortunately, that is relatively easy to fix.

Solution:

  • A big relief in the orientation of an Excel spreadsheet is the visualization of the page breaks

See picture (click to enlarge)

Show boundary line
Advertisement
  • You can also choose here whether you want the spreadsheet in portrait or landscape format. According to the selection then also change the boundary lines.

See picture (click to enlarge)

Select alignment
Advertisement
  • By default, anything beyond this edge is no longer visible in the printed area and is printed on a second page.
  • So we have to set the print area here first.
  • For this we mark the entire area of the table to be printed.

See picture (click to enlarge)

Set print area in Excel
Advertisement
  • In our example, we left the page layout in portrait orientation and set the print area accordingly.
  • If we now want to print our spreadsheet and the margin is still wrapped on 2 pages due to the extra width, we can easily adjust this by adjusting the scaling in the print settings to “show sheet on one page”.

See picture (click to enlarge)

Adjust print output
Advertisement

A common problem with printing Excel spreadsheets is that only part of the required content is printed out.

Fortunately, that is relatively easy to fix.

Solution:

  • A big relief in the orientation of an Excel spreadsheet is the visualization of the page breaks

See picture:

Show boundary line
Advertisement
  • You can also choose here whether you want the spreadsheet in portrait or landscape format. According to the selection then also change the boundary lines.

See picture:

Select alignment
Advertisement
  • By default, anything beyond this edge is no longer visible in the printed area and is printed on a second page.
  • So we have to set the print area here first.
  • For this we mark the entire area of the table to be printed.

See picture (click to enlarge)

Set print area in Excel
Advertisement
  • In our example, we left the page layout in portrait orientation and set the print area accordingly.
  • If we now want to print our spreadsheet and the margin is still wrapped on 2 pages due to the extra width, we can easily adjust this by adjusting the scaling in the print settings to “show sheet on one page”.

See picture:

Adjust print output
Advertisement

2. New line within a cell.

2. New line within a cell.

If you frequently create Excel spreadsheets (eg invoice template) in which you also find a lot of text, you will eventually encounter the problem that if the cells are wrapped automatically the text will be wrapped exactly where you can not use it at all.

Solution:

  • You can also edit anything you want to enter text in a cell in the formula bar.
  • First select the cell in which the text to be edited is located.
  • Next, expand the viewport of the formula bar.

See picture (click to enlarge)

Expand the edit bar
  • Now place the cursor directly in front of the first letter of the word which should be wrapped in the next line. (In our example “in”)

See picture (click to enlarge)

 

Position the cursor
Advertisement

Now press the “ALT” key first, keep it pressed, then press the “ENTER” key.

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

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

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

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

If you frequently create Excel spreadsheets (eg invoice template) in which you also find a lot of text, you will eventually encounter the problem that if the cells are wrapped automatically the text will be wrapped exactly where you can not use it at all.

Solution:

  • You can also edit anything you want to enter text in a cell in the formula bar.
  • First select the cell in which the text to be edited is located.
  • Next, expand the viewport of the formula bar.

See picture:

Expand the edit bar
  • Now place the cursor directly in front of the first letter of the word which should be wrapped in the next line. (In our example “in”)

See picture:

 

Position the cursor
Advertisement

Now press the “ALT” key first, keep it pressed, then press the “ENTER” key.

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

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

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

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

3. Save Excel spreadsheet as PDF.

3. Save Excel spreadsheet as PDF.

if you have created an Excel spreadsheet and would like to send the computed results (for example, by email) to someone who may not be using Excel or a non-compliant version. It may be helpful to convert them into the familiar PDF format.

Solution:

After you’ve created your spreadsheet, do not just click Save, because Excel will use the default xlxs file format instead of “Save As” and then choose the “PDF” file format

See picture (click to enlarge)

Advertisement

Tip:
The easiest way to get to the save options is right out of the worksheet the key “F12”

if you have created an Excel spreadsheet and would like to send the computed results (for example, by email) to someone who may not be using Excel or a non-compliant version. It may be helpful to convert them into the familiar PDF format.

Solution:

After you’ve created your spreadsheet, do not just click Save, because Excel will use the default xlxs file format instead of “Save As” and then choose the “PDF” file format

See picture:

Advertisement

Tip:
The easiest way to get to the save options is right out of the worksheet the key “F12”

4. Protect Excel table with password.

4. Protect Excel table with password.

If you have created an Excel spreadsheet, it is often stored on a drive (such as a network) that many people within this network have access to. However, not everyone should get access to all files in this drive.

Therefore, it can be helpful to protect certain files against 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 picture (click to enlarge)

Encrypt the workbook
Advertisement
  • Here you assign now any password, which is needed later to open the file.
    (Please remember the password exactly, otherwise it will not be possible for you to open the file without this password!)

See picture (click to enlarge)

Assign password in Excel

Note:
The file is still visible to everyone, but only with the valid password (case-sensitive) is the workbook open!

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

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

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

If you have created an Excel spreadsheet, it is often stored on a drive (such as a network) that many people within this network have access to. However, not everyone should get access to all files in this drive.

Therefore, it can be helpful to protect certain files against 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 picture:

Encrypt the workbook
Advertisement
  • Here you assign now any password, which is needed later to open the file.
    (Please remember the password exactly, otherwise it will not be possible for you to open the file without this password!)

See picture:

Assign password in Excel

Note:
The file is still visible to everyone, but only with the valid password (case-sensitive) is the workbook open!

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

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

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

5. Enter mathematical signs in Excel.

5. Enter mathematical signs in Excel.

Since Excel is a spreadsheet program, not a word processor, some of the text-related inputs are a little hard to implement as it would the user.

Example:

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

The problem now after you type the equals sign and your text and confirm with Enter is that Excel assumes that you want to enter a formula, because all the formulas in Excel start with an “=” character. The following error message then appears: “#Name?”

Solution:

  • In the cell in which you want to start with the “=” character, first use the key combination “ALT GR” + “single quote (‘)” to insert an invisible apostrophe.
  • This is not displayed, and also not printed.

Note:
This approach applies to all mathematical signs such as +, -, /, and so on.

Since Excel is a spreadsheet program, not a word processor, some of the text-related inputs are a little hard to implement as it would the user.

Example:

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

The problem now after you type the equals sign and your text and confirm with Enter is that Excel assumes that you want to enter a formula, because all the formulas in Excel start with an “=” character. The following error message then appears: “#Name?”

Solution:

  • In the cell in which you want to start with the “=” character, first use the key combination “ALT GR” + “single quote (‘)” to insert an invisible apostrophe.
  • This is not displayed, and also not printed.

Note:
This approach applies to all mathematical signs such as +, -, /, and so on.

6. Display formulas in Excel.

6. Display formulas in Excel.

Anyone who has ever created an extensive Excel spreadsheet with many calculations, knows that at some point the point comes, where one no longer knows exactly in which cells calculations were made, and where numbers were entered manually.

Would not it be helpful to know in which cells there are formulas everywhere?

Solution:

  • Under the tab, choose: “Formulas” / “Show formulas”

See picture (click to enlarge)

Show formulas in Excel
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
  • Here you can see exactly where in your spreadsheet formulas are stored everywhere.

Anyone who has ever created an extensive Excel spreadsheet with many calculations, knows that at some point the point comes, where one no longer knows exactly in which cells calculations were made, and where numbers were entered manually.

Would not it be helpful to know in which cells there are formulas everywhere?

Solution:

  • Under the tab, choose: “Formulas” / “Show formulas”

See picture:

Show formulas in Excel
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
  • Here you can see exactly where in your spreadsheet formulas are stored everywhere.

7. Show calculation path in Excel.

7. Show calculation path in Excel.

If you have created a table with some calculations and cell references, you can display the cells where formulas are stored everywhere, as shown in Tip # 6.

However, the longer a formula is, the harder it will be to look for all the cells specified on the sheet, and to find a mistake. Would not it be practical if the lane that led to the calculation could be displayed?

Solution:

  • Select a cell in which calculations were performed by Excel, and then select in the tab: “Formulas” / “Track to Predecessor”

See picture (click to enlarge)

Show track to predecessor in Excel
  • Here you will now be shown with the arrows with which cell values the result in the marked cell has come about.

Note:
Of course, if too many cell references are included in the result of a cell, it will become cluttered up again, but for smaller calculations, this tip can be quite helpful.

If you have created a table with some calculations and cell references, you can display the cells where formulas are stored everywhere, as shown in Tip # 6.

However, the longer a formula is, the harder it will be to look for all the cells specified on the sheet, and to find a mistake. Would not it be practical if the lane that led to the calculation could be displayed?

Solution:

  • Select a cell in which calculations were performed by Excel, and then select in the tab: “Formulas” / “Track to Predecessor”

See picture:

Show track to predecessor in Excel
  • Here you will now be shown with the arrows with which cell values the result in the marked cell has come about.

Note:
Of course, if too many cell references are included in the result of a cell, it will become cluttered up again, but for smaller calculations, this tip can be quite helpful.

8. Single cells in Excel with read-only protection.

8. Single cells in Excel with read-only protection.

If you have created a table and you want to share it (eg in a network), and you want to be able to edit only certain areas, but the rest of the table can only be modified by yourself, then you should protect these cells with a write protection

Solution:

  • Basically, all cells in Excel are marked with “Locked” in the formatting. That if you now provide the worksheet with a write protection, nothing can be changed at all.
  • First select all cells or cell areas which should later be changeable for all employees who have access to the file.
  • Then right-click to activate the context menu above the marked cells and select “Format cells”.

See picture (click to enlarge)

 

Format cells in Excel
Advertisement
#1
WD Elements Portable, Externe Festplatte - 2 TB - USB 3.0 - WDBU6Y0020BBK-WESN
WD Elements Portable, Externe Festplatte - 2 TB - ...*
by Western Digital

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

*Zuletzt aktualisiert am 17. August 2018 um 12:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
  • Next go under the “Protection” tab in the cell formatting menu, and remove the “Locked” checkmark there.

See picture (click to enlarge)

Remove write protection in Excel
  • In the final step, under the “Review” tab, we go to “Protect Sheet” where we (optionally) assign a password, and set the respective hooks for the processing restrictions we want to assign to these cells.

See picture (click to enlarge)

Protect Excel worksheet

If you have created a table and you want to share it (eg in a network), and you want to be able to edit only certain areas, but the rest of the table can only be modified by yourself, then you should protect these cells with a write protection

Solution:

  • Basically, all cells in Excel are marked with “Locked” in the formatting. That if you now provide the worksheet with a write protection, nothing can be changed at all.
  • First select all cells or cell areas which should later be changeable for all employees who have access to the file.
  • Then right-click to activate the context menu above the marked cells and select “Format cells”.

See picture:

 

Format cells in Excel
  • Next go under the “Protection” tab in the cell formatting menu, and remove the “Locked” checkmark there.

See picture:

Remove write protection in Excel
  • In the final step, under the “Review” tab, we go to “Protect Sheet” where we (optionally) assign a password, and set the respective hooks for the processing restrictions we want to assign to these cells.

See picture:

Protect Excel worksheet
Advertisement

9. The error correction in Excel.

9. The error correction in Excel.

Who does not make mistakes, does not work, says the vernacular. And there is actually something in that wisdom. After all, it happens that you just “clicked” on a certain spot, and suddenly everything looks completely different than a second ago. Luckily, Excel 2016 offers you the opportunity to undo up to 100 work steps.

Solution:

  • At the top of the worksheet (directly above the “Start” tab), go down to the small arrow and view the last steps taken.
  • Now select from the displayed list of the last work steps, how many steps you would like to undo.

See picture (click to enlarge)

Excel-Undo
Advertisement

Note:
In most cases, the error is detected immediately and you want to undo it quickly. In that case, just use the following keyboard shortcuts:

  • “CTRL” + “Z” (undo last step)
  • “CTRL” + “Y” (another step forward)
Print Friendly, PDF & Email

Who does not make mistakes, does not work, says the vernacular. And there is actually something in that wisdom. After all, it happens that you just “clicked” on a certain spot, and suddenly everything looks completely different than a second ago. Luckily, Excel 2016 offers you the opportunity to undo up to 100 work steps.

Solution:

  • At the top of the worksheet (directly above the “Start” tab), go down to the small arrow and view the last steps taken.
  • Now select from the displayed list of the last work steps, how many steps you would like to undo.

See picture:

Excel-Undo
Advertisement

Note:
In most cases, the error is detected immediately and you want to undo it quickly. In that case, just use the following keyboard shortcuts:

  • “CTRL” + “Z” (undo last step)
  • “CTRL” + “Y” (another step forward)
Print Friendly, PDF & Email
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
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . 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
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T11:48:11+00:00By |Categories: Excel 2016, Office 2016|
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