Private Excel course via Skype - according to your individual needs

3 secrets about Excel time management

Excel time management often causes a despair: instead of a date, a number is displayed, e.g. 41615. It depends on the cellformat.

Excel stores dates as a serial number. By default, January 1, 1900 is equivalent to number 1, and for example, 1st January 2013 is 41275, or 22th September 2017 is 43000, and 18th July 2020 will be 44000!

Change a cell’s number format to Date

First select the cell(s) containing the numbers.
Then press Ctrl 1 to open Format Cells dialog box.

In the Number group choose ‘Date’ and then select the desired format.

Check the Spam box above, to see if it fits to your idea.

The fact that dates are numbers adds up to 3 more exciting questions:

1. What is the first and last date you can enter in Excel?

The first number is zero, which is 00-01-1900, and the first date (1) belongs to 1st January, 1900.

The last date is 31-12-9999, which is numbered 2958465!

With this nearly 3 million date, you can make simply calculations, and know further information about each day, such as which day of the week it is.

Of course, you can record any previous or later date as a text, but you can not count with them.

2. Excel time management: what about decimal places?

Since coding is 1 = 1 day = 24 hours, then 0.5 = 12 hours, 0.75 = 18:00 and 1/3 = 0.33333 day = 8 hours.

Rounding should be treated cautiously as 0.001 = 1 minute 26 seconds.

One day is (24 x 60 x 60 = ) 86.400 seconds, so one second (1/86400) is 0.0000116.

This is due to the fact that not only the days but also the dates can be counted. For example, you can calculate your work time record by subtracting departure and arrival time, e.g.: 17: 00 – 8: 00 = 9:00

To see time in a correct way, choose from Format Cells (Ctrl 1) / Time options.

Tip: Do you need to record the exact time in a cell? Press Ctrl Shift : buttons, and it will insert current time in the cell.

3. How to format the 24:00 or more hours?

If you choose Time format, you won’t see days. This can be a problem, if you have more than 24 hours, for example, when working hours are aggregated.

To see the exact hours, use Custom Format in the Cell Formatting window, and change h:mm to [h]:mm

Do you have other questions about Excel dates?

For more information on Excel time and date formats, visit Microsoft’s page. If you want to get specific exercises and get personalized tips, take a private Excel course!

7 easy-to-remember Excel shortcuts that can save you hours

Okay, Excel shortcuts are useful, but how do you remember so many letters? It’s best to stick to something. (You are lucky, because you understand English, and it’s easy to remember the words. But I mostly teach Hungarians, so I also have teach them some English as well.)

Here are 7 easy-to-remember Excel shortcuts:

You’ve probably seen poker players risking everything at Excel shortcut to select ALL is Ctrl A bet, saying “All in”.

You can easily remember the shortcut to select everything (all cells) in an Excel table is Ctrl A (all).

It marks the entire table with one click – if you are “inside” the table. In Word or Internet, it also selects the full text.

~ ~ ~ ~ ~ ~ ~ ~ ~

I guarantee that you will love this one, once you learn:

Ctrl Z = undo

One of the rare combinations that does not come from English, but rather is visual: You know, when you write something and fail, the strikethrough is similar to the letter Z.

(German rumors says, it comes from the word Zurück)

Fortunately, this one is universal, too, so you can use it in other programs as well. For example, sometimes it happens to me that I delete a text in Gmail with a bad gesture – and there is no Undo button – but I don’t panic at all, just hit a quick Ctrl Z.

One disadvantage of this combination that it doesn’t work in life …

~ ~ ~ ~ ~ ~ ~ ~ ~

You know the proverb: He that seeks, finds.

One of the most practical shortcuts is Ctrl F (find), which can help you to find quickly, what you are looking for.

You can use not only in Excel to search, but also in Word, pdf, and the Internet, and even in the File Manager.

~ ~ ~ ~ ~ ~ ~ ~ ~

Did you know the easiest way to start printing is pressing a Ctrl P ? (print)

In 2007, the print panel pops up immediately, but in newer versions, Ctrl P opens the Print Preview.

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

Excel shortcuts to scroll the table A major part of using the mouse comes from scrolling down. Switch to the PGDn (page down) key and try to see how it feels to scroll through it.

The real breakthrough will happen when you start using the Ctrl key as well: Ctrl PgDn will take you to the next worksheet, and Ctrl PgUp to the previous one.

This also works on the internet: use the Ctrl PgUp (PgDn) keys to switch the open pages.

~ ~ ~ ~ ~ ~ ~ ~ ~

Shortcut: Shift arrow The Shift – arrow combination is like pulling a mouse: it increases the size of the selected cells.

Try all 4 directions to learn the shortcut. If you select more cells, use the opposite arrow to decrease the area.

Extra Tipp: hold the Ctrl key, too: Ctrl Shift ↓ will select the column until the last data.

~ ~ ~ ~ ~ ~ ~ ~ ~

Shortcut key: F4 If you use “dollar” in the formula, it is simplier to switch between relative-absolute references with F4 than to type $.

(This is not a keyboard shortcut, but a function key, but the benefits are unbeatable!)

In the English keyboard $ is next to the number 4. You can also note “4” that there is a 4 “position” for the dollar, eg A2, $A$2, A$2 and $A2

The Dollars feature is active only when you’ve entered the reference.

More Excel shortcuts

To read about Excel shortcuts is not enough, you have to be persistent in practice. As in any new habit, you have to overwhelm your old bad habits. If you need help and a good mentor, come and learn with us, take Privat Excel Lessons through Skype.

How can you add comments to an Excel table?

I’ve seen others add comments, insert notes into Excel spreadsheets – like post-it or sticky notes. How can I do the same? Can it be printed?

Answer: right-click on the cell and click Insert Comment.

Add comments to your file

You can add comments using the right mouse button or use Shift F2 shortcut. First, they look spectacular, take my advice, rather avoid using the comments. Instead, put the text into a separate cell – make a Comment column – and you can easily filter or sort these information.

If there is a comment in a cell, it is indicated by a small red triangle in the upper left corner of the cell. Your text will be read when you move your mouse over the cell.

You can rename or delete the comment by right-clicking the cell and click Edit Comment or Delete Comment.

Show all comments

If you want to see all the comments at once, click the Review tab, and select ‘Show all comments’ in the Comments group, and

Use the Previous / Next buttons to scroll through the comments in the table one by one.

Print comments

You can also print comments as they appear (or not appear) on the sheet, or at the end of the page listing which comment is in which cell.

Display comments as you want to print them:
- right-click the cell containing the comment, and then click Show/Hide Comments
- display all comments (Review tab/Show All Comments)
- you can move or resize comments by clicking the border of the comment box
Open the Page Setup dialog box (from the Print Preview – Ctrl P – or the Page Layout tab) and click the Sheet tab.

In the Comments box, select As displayed on sheet or At end of sheet.

More Excel secrets

With some customized tutorials you can be much more efficient in your work, saving half an hour or two hours a day. If you need help with Excel learning, we would be happy to help you on a Privat Excel Lessons through Skype.

The 3 best FIFA World Cup Excel Templates

Make or get a 2018 FIFA World Cup Excel Template for recording results! Following the matches is a great way to practice Excel! I recommend 3 great Excel templates.

The point is to use Excel and learn from it. Good experiences (like the FIFA World Cup) can change your attitude to Excel, and also help you remember more information (Excel features), since you are emotionally or intellectually tossed.

Make your own World Cup Excel template!

First I recommend you to create an Excel table on your own, that reflects your thinking and preferences. If the whole task is too big, just model subtasks!

You can practice the following:

Create a database: for example make teams and players lists, schedule of the matches
Write formulas: Calculate the difference in the goal or HA to the forward team
Color by Conditional formatting: you can automatically color good results.

You can find the details and rules of the 2018 FIFA World Cup on the Wikipedia page.

Use ready-made World Cup Excel templates! I show you 3 of them

If you do not want to start recording and editing now, or you need some inspiration, I recommend you these Excel Templates. You can track not only the results, but also write down your tips, and plan the result of your favorite team.

Excely.com World Cup Excel Template

Download file from excely.com, there are several templates for sports events, and track all matches of the international football tournament.

Set language on the “Settings” tab (cell C4). There are also Hungarian, so you can find your own language, too. You can write it, or choose from the drop-down list.
You can also set your time zone to GMT + 1:00, so you will see exactly the time of the matches.
Enter the final result of the games on the main table (2018 World Cup), and everything will be automatic: the winner is marked in blue, and the losing team will become red in. Excel calculates the scores for the Football Team according to FIFA standards – without macros.

Eexcel.co.uk World Cup Excel Template

I also recommend the template of the eexcel.co.uk football which you can download by clicking here.

You can also set the time zone on this template, although the days are unfortunately not shown properly – on my Hungarian version.

On the BOOKIE tab, you can record bets with 3 players, which will be immediately evaluated. If you want to play more, you have to pay for the 20-50-100 version (a symbolic € per person).

Chandoo.org FIFA Worldcup 2018 Excel Tracker

With this Excel Tracker (click and download from here) you can view summary and detailed points table, and also have a bracket and timeline view.

In the settings, you can give you favorite teams, so you can follow them throughout the World Cup.

More Excel secrets

Please share this article with those who may get closer to Excel with these templates.

If you need help with Excel learning or interpretation of templates, we would be happy to help you on a Privat Excel Lessons through Skype.

What is an Excel Hyperlink, and how to use them?

With an Excel Hyperlink you can link another file or website to your Excel file, so you don’t have to search for it again and again. Or if the Excel file or Word document is too large, you can make a link inside the file, so you can jump from one point to another with a single click.

It’s simplier than you think, and it works in Excel, Word, Outlook and even on the Internet (for example, online email or web site editors) and it even has a hotkey!

Insert an Excel hyperlink

The name of the “link” in Office programs is “hyperlink”. To insert a link:

First, select the part you want to assign the hyperlink to. This can be an Excel cell, any length of text, or even a shape (eg image or button).
Use Ctrl K (on Mac: Cmd K) shortcut to locate the link. If you are searching for the icon on the Ribbon: Insert tab / Hyperlink button.
Fill out the fields.

4 main types of Excel Hyperlink

On the left you can choose from 4 options:

An existing file or web page
The specific point of a document, eg. a specific cell or named range in an Excel worksheet or file
New document
Email Address: By clicking this button, you automatically open an email with this address in your email.

Excel Hyperlink function

In Excel, there is a function called HYPERLINK. You can use this to make texts as clickable links. The function has 2 parts: HYPERLINK(link_location, [friendly_name])

Link_location: The hyperlink itself, the path and file name to the document or webpage you want to open. Link_location can be a referred from a cell, or it can be a text string enclosed in quotation marks.
Friendly_name: It is the text that appears as a link. It’s optional, in case of missing, the link itself will appear. Friendly_name will be displayed in blue and is underlined.

For example: =HYPERLINK(A2; “link”)

You can specify text, cell references, and more complex formulas in both parts, for example, you can paste text from different cells.

For example: =HYPERLINK(A2; B2 & ” company contract”)

Use Excel hyperlinks, and make your own life and your colleagues’ lives easier, as you don’t have to spend hours with searching.

If you would like personal help with Excel, come and learn Excel with us via Skype.

Tricky secret to move cells in Excel

Move cells and columns with your mouse

If you want to copy cells or columns to a nearby location, simply select the cells and then hold the mouse over the border. In this case, the 4-way arrow is displayed, which means that you can move the cells. Simply move them with the mouse (drag-and-drop).

move cells easily If you also press Ctrl, the arrow changes to +.

If you are moving the range this way, the original cells will be preserved and there will be a copy on the other place, too.

Always pay attention to the shape of the mouse as it indicates that you are moving or copying the selection.

Changing columns, or move columns within the table

move cells simply If you press the Shift button while moving the cells, you can move the column (or cells) so that it insert additional cells – and doesn’t overwrite the original data.

In this case, a gray “strip” shows where the data is transferred. (In the small gray window, the exact cell range is displayed.) As you release the mouse (still hold down the Shift key), the cells will slip right, and the cell or column exchange is ready.

More Excel secrets

Come and learn with us, take Privat Excel Lessons through Skype.