Excel || 15 Tips and Trics

Introduction

If you have read my profile, you know that I like to share tips and trics of Excel. And that's why my first blog is about Excel. Those tips and trics will save you a lot of time and will increase your knowledge of Excel.

I recommend to try them all but especially tip #13 and #14 are real time savers. Enjoy!!

Jan
https://www.ExcelXL.nl

#1 Duplicate a worksheet as fast as possible?

Hold down the ctrl key and drag the original sheet tab.

During this process you will notice a plus (+) sign

If the name of the original sheet is "sheet 1", the dragged sheet will be named as "Sheet 1 (2)".

#2 Rename a worksheet

1. ·Double click on the sheet
2. ·Type the new name
3. ·Enter

If you want to work a little slower, use the following steps:

1. ·Right click on the worksheet
2. ·Rename
3. ·Type the new name
4. ·Enter

#3 Sheet colour

To create a great overview in case of several sheets you can give every sheet a colour.

1. ·Right click on the worksheet
2. ·Tab colour
3. ·Select your favorite colour

When working with more than 2 sheets, use two colours; e.g. the even sheets red and the odd sheets blue (my favourite combination)

#4 Insert a new sheet (fast method)

Shift + F11

The new sheet appears at the left of the selected sheet.

In version 2007 and later you can also use the icon right to the last sheet. When you move over this icon with your mouse you will also see the shortkey tip (Shift + F11).

Although, there’s a difference. By using the icon, the new sheet will appear at the right side of the last sheet.

#5 Moving from one sheet to another

Use Ctrl + page down (moving up)

Use Ctrl + page up (moving down)

#6 Want to select an entire row?

Select a cell and use Shift + spacebar

#7 After reading #6 I guess you wonder how to select a whole (single) column.

You do? Well, select a cell and use Ctrl + spacebar

#8 Want to join text (or numbers) from two or more cells together?

Use "&" instead of "Concatenate". An example to explain both ways.

Cell A1 jan
Cell B1 bolhuis

Goal Cell C1: jan bolhuis

Two ways to go:

1. =CONCATENATE(A1;" ";B1) result: jan bolhuis
2. =A1&" "&B1 (will lead you to the same result).

But wait........ You want to see each first letter as a capital!? Well, use a combination with "PROPER"

First I give an example with the function "PROPER" and after that you will see the combination.

In Cell C1 use the next formula to see "Jan" instead of "jan":

=PROPER(A1)

To use this formula with more cells and in combination with "&" or "Concatenate" you can follow the next steps.

1. =PROPER(A1&" "&B1) or
2. =PROPER(CONCATENATE(A1;" ";B1))

Isn't that amazing? :)

#9 Excel 2010 Paste Special

Ctrl + Alt + V = Paste Special dialog box

#10 part 1 Want to use a date as text?

Use the following formula (The date is in cell A1)

=TEXT(A1,"dd-mm-yyyy").

Users with a Dutch version have to use ; (semicolon) instead of , (comma).

=TEKST(A1;"dd-mm-jjjj") (for Dutch users only).

Between the quotes you can change the format you like e.g. "dd/mm/yyyy" or "dd mmmm yy". Up to you :)

#10 part 2 Another way to show dates

If you're not a Dutch user of Excel, please use a comma instead of a semicolon.

Another way to show date as text is by using the WEEKDAY() function. This one doesn't work for months or years. But maybe it does J

1. Cell A1 contains the date
2. Put the formula in cell B1: = WEEKDAY (A1)
3. Go to "Format Cells" (tip: shortkey is "Ctrl + 1")
4. Go to the tab "Number" and select "Customer"

Now you will see by "Type" the words "standard"

Select this one and change it into "dddd"

#11 Ctrl + Enter / Shift + Enter

Enter puts the result of a formula or text into a cell and selects the cell below.

Ctrl + Enter puts the result of a formula or text into a cell and keep the cell selected (Saves one click comparing to Enter).

Shift + Enter puts the formula or text in the cell and jumps the cursor up one cell above.

#12 Alt + Enter

Alt + Enter in a cell creates a second line.

#13 Autosum

1. Alt + = Autosum You need to confirm with Enter or (prefer) Ctrl + Enter
2. Alt + = = Autosum You don't need to confirm with Ctrl + Enter.

Take a pause between the two equal signs to check whether the range is correct or not.

#14 Number Formatting

Here's the "complete list" of number formatting. Shortkeys ofcourse. Enjoy!

1. Ctrl + Shift + ~ General Number Format (doesn't work in the Dutch version; You can use Ctrl + M)
2. Ctrl + Shift + ! Number Formatting (Comma style - ,00)
3. Ctrl + Shift + @ Time Number Formatting
4. Ctrl + Shift + # Date Number Formatting
5. Ctrl + Shift + $ Currency Number Formatting
6. Ctrl + Shift + % Percentage Number Formatting

#15 Refreshing All Pivot Tables in One Click:

1. Go to Data Ribbon
2. Click on Refresh All button (or press CTRL+ALT+F5)