Excel Tips: 38 extremely useful tips + shortcuts

The skill of using Excel skillfully and mastering Excel tricks will be of great help to your advancement or study. Knowing that problem, TipsMake offers a series of 38 extremely useful Excel tips, helping you become more and more professional in the eyes of friends and colleagues.

1. Excel trick - Smart search in Excel

Surely you already know the search function in excel by pressing Ctrl + F or Home - Find, but in case you are not sure about the search results?

TipsMake for example, if you want to search for the phrase 'Smart Chemical Joint Stock Company' in the general diary book on excel, but you can't remember the word 'Minh' but only the Company . Update . something, and in particular, something you don't remember.

In this case, maybe not, but fortunately, thanks to the intelligence of excel and your cleverness, you can apply the following excel trick:

- Step 1: Turn on the search function in excel by clicking the Find commands on the Ribbon or using the key combination Ctrl + F

- Step 2: Enter the phrase: Company * Update *

---> Enjoy the results!

Excel Tips: 38 extremely useful tips + shortcuts Picture 1

Smart search in Excel

2. Excel Tricks - Create a quick access key in the Quick Access section

In Excel there is a toolbar called 'Quick access toolbar' - as the name implies, these tools help us access Excel's functions quickly such as undo, redo, opening new files. , save file, print fast,…. These tools usually appear on the top left of the excel interface as shown below:

Excel Tips: 38 extremely useful tips + shortcuts Picture 2

Create a quick access key in Quick Access 1

As you can see in the image above, you can see that on the excel sheet above, the quick access toolbar only has shortcut shortcuts, undo and redo, to add other functions, click on the drop-down arrow and tick to select the functions you want such as' New 'to open a new file,' Open 'to open an existing file,' Quick Print 'for quick printing,' Print Preview 'to display before printing, or' more commands' to create any additional functions you want in excel.

Excel Tips: 38 extremely useful tips + shortcuts Picture 3

Create a quick access key in Quick Access 2

As you can see in the image above, you can see all the commands in Excel and we can add or remove any commands by selecting that command and clicking add or remove and then OK.

3. Excel trick - Add a diagonal line in a cell (cell)

There are many cases that may need to use this excel trick, usually you use it to create a diagonal line that aims to divide different attributes for any cell or a row or column. TipsMake, for example, you want to create a diagonal line separating two parts of the debit account and have it in the general diary in excel for it to look as beautiful as the image below:

Excel Tips: 38 extremely useful tips + shortcuts Picture 4

Create a diagonal in an excel cell 1

You do the following: In the box you want to diagonal, as shown in the picture is cell E4 -> you right-click on cell E4 then select Format Cell> click the Border tab, then click the button with the diagonal shape as shown below.

Excel Tips: 38 extremely useful tips + shortcuts Picture 5

Create a diagonal in an excel cell 2

4. Excel trick - Select and add column rows entirely using the keyboard

The use of a keyboard, not a mouse, will help speed up your work in Excel quite a lot. Therefore, instead of using excel as both input and drag and drop to add and remove columns, you can use the following extremely useful keyboard shortcuts:

Step 1: To add or remove any row or column, you must select that row or column first.

- Select the line: when your cursor is at any position on that line, press Shift and spacebar

- Select column: when your cursor is at any position on that line, press Ctrl and spacebar

Step 2: To add or remove lines:

- Add lines / columns: press Ctrl + Shift + +

- Decrease lines / columns: press Ctrl + -

Although you know how to use this shortcut combination, but if you just select a line and then press ctrl shift + repeatedly, it is really very tired. It is best to add a number of x rows or x columns, select exactly the same x number then add. You can select the x lines you want by pressing the arrow keys to move them up and down, left and right, noting how many columns you have selected. For example, if you select 10 rows, then press ctrl shift + will insert 10 more rows, select 100 columns will be added 100 columns, just duplicate so .

Excel Tips: 38 extremely useful tips + shortcuts Picture 6

5. Excel trick - Copy or move data regions extremely fast

To move or copy data from a column in an excel spreadsheet, the fastest way is:

Step 1: First, select that data column. Then move the mouse to the edge until the mouse icon turns into 4 arrows pointing in 4 directions.

Step 2:

- To move the data area you just need to hold the mouse and drag.

- To copy data area, press Ctrl key before dragging

Excel Tips: 38 extremely useful tips + shortcuts Picture 7

6. Excel trick - Delete blank cells in a data range

When entering data in excel there will be many cases where there are gaps in a data area. For example, the image below:

Excel Tips: 38 extremely useful tips + shortcuts Picture 8

As you can see there are 2 lines E11 and E14 empty inventory list, in case you want to delete those 2 lines, you can select those 2 lines and click delete. But in case there are a lot of blank lines or blank cells, you cannot do that very time consuming. You can apply the following excel trick:

Step 1: First scan all the data area containing the spaces you want to delete, then you turn on the Filter filter function in excel, click the drop-down arrow in any column. Once there, a menu will appear, you uncheck Select All and select the Blank at the bottom. Then all blank cells / blank lines will be immediately displayed.

Excel Tips: 38 extremely useful tips + shortcuts Picture 9

Step 2: Highlight all the blank lines that appear, then right-click and select Delete Rows - Then filter is complete. And this is the result:

Excel Tips: 38 extremely useful tips + shortcuts Picture 10

7. Excel trick - Application to filter Excel into an accounting book

The filter function in excel is a function that accountants or anyone who uses excel cannot not know. The longer you work with Excel, the more you will see the value of filtering by Filter to manage.

TipsMake for example at the end of the month, you need to filter 112 account in the General Journal to track or edit something for example. You scan all data in the account column area. Then press Ctrl + Shift + L, then just click the drop down arrow and select the account you need to filter is done.

Excel Tips: 38 extremely useful tips + shortcuts Picture 11

8. Excel trick - Limiting input values ​​using Data Validation in Excel

In order for you to understand this part of TipsMake, for example in an excel sheet, you have a commodity list column with a price column on the side for example. You want to limit this price range to less than 20,000, for example, because it is likely that you enter data while you are drunk, for example, you can mistype from 20,000 to 200,000 or 2,000,000 VERY RISK. So now it is best to use the trick to limit the input value using the Data Validation function in excel. Follow these steps:

- Step 1: Highlight the data area to be limited, click on the tab 'Data' on the menu bar - then go to Data Validation.

Excel Tips: 38 extremely useful tips + shortcuts Picture 12

- Step 2: In the Allow section select 'Whole Number', the Data part to 'Between', the Minimum and Maximum parts for 1 and 20000 respectively press ok to finish.

-> As you can see in the picture below, in the bottom box in the price column, TipsMake to 30000 value so Excel is reporting an error that cannot be entered, so you have succeeded.

Excel Tips: 38 extremely useful tips + shortcuts Picture 13

9. Excel trick - Master the basic operations in Excel

No matter how many excel tips you have, you cannot master the basics, but you cannot become a true excel expert in the eyes of your friends. Perhaps only occasionally pulling out the tricks to the world and the nature, I do not understand anything. Especially for those who want to learn more about excel, try to master these basic skills already:

+ Create spreadsheets, Format data in excel: Format numbers, tables, merge, delete (split) .

+ Automatic format: Format as required

+ Using common functions: Vlookup, If, Hlookup, .

+ Draw Charts, Graphs: Chart

+ Create hyperlink: Hyperlink

+ Print Settings: Page setup

+ Set Password for Excel files of all versions.

10. Excel trick - Managing goods with Data Validation in Excel

First TipsMake please give the example you have a warehouse to manage in excel in the style:

Excel Tips: 38 extremely useful tips + shortcuts Picture 14

For example, you have a inventory table with 3 columns: "Product type", "Product life", "Product status". and you want to manage in excel like:
- Column "Product type" selected in a drop-down menu, click based on an available list, avoiding manual data entry. For example, the list of products for iphone 6, iphone 7, sony z3 z5, .
- Column "Product lifetime" we need the corresponding drop-down menu but must meet a condition matching column "Product type". For example, the iPhone product category column, then the product menu in the drop down menu will appear for the corresponding products such as iphone 100, iphone 1000, .

- Column "Product status" has 2 categories "in stock" and "out of stock" only.

To do that, first need to understand the Data Validation function in excel, this trick is quite complicated, specifically you see at:

11. Excel tips - Speed ​​up input hundred times

Surely you do not expect to have an excel trick to speed up your input to dozens, hundreds, even thousands of times if you have to regularly enter data on excel with long and lengthy content. and again and again. That is the trick to take advantage of the auto correct function in excel

A very simple example is that you often have to enter the words 'Socialist Republic of Vietnam' or 'TipsMake' for example. Instead of having to type continuously like that, you just need to use the shortcut function in excel with a very cool key combination that is Alt + T + A. Then enter the text you want to abbreviate in the 'With' section and the word you want to replace in the Replace section. For example: In the replace part you enter is cvn, the part with you enter is 'Socialist Republic of Vietnam' for example.

Excel Tips: 38 extremely useful tips + shortcuts Picture 15

12. Excel tips - See quick statistics of sum, mix, max, .

Probably a lot of excel users know about the common excel functions about statistical calculations such as sum to sum, min max to calculate the largest minimum value in a certain data range, . Because you have to use the function, you can view quick statistics about the data of a certain data area by scanning the data area and viewing it under the status bar 'Status bar', for example:

Excel Tips: 38 extremely useful tips + shortcuts Picture 16

13. Excel tips - Pair data with &

To help you understand this very basic but very useful excel trick, TipsMake gives an example that you have 2 separate cells separated into 2 parts that are first and last names, for example A1 is Dao Dang, cell B1 is Khoa, now in cell C1 you want to return the result of full name is Dao Dang Khoa without having to manually enter it, you can apply this excel trick by writing in the box C1 is: A1 & B1, or you can add a space in the middle for beauty: A1 & '' & B1, the result will be returned: Dao Dang Khoa.

14. Excel trick - Rename the sheet by double clicking

There is an extremely simple trick in excel that many people do not know, it is renaming a worksheet (Excel) in excel with just a double click, instead of right clicking to select rename or doing any other operations. any other.

Excel Tips: 38 extremely useful tips + shortcuts Picture 17

15. Excel trick - Repeat the Format Painter operation

For those who often use the 'paint brush' (Fomart Painter) in excel, this is a great feature in excel, all you need to do is simply instead of just clicking once on the then you double-click the mouse, then scan the format on the cells or data fields you want to copy the format. Try it now and always!

16. Excel tips - Convert rows to columns and vice versa

In order for you to understand this tip, the TipsMake Institute of Accounting and Informatics Training Institute has a specific example.You want to convert a table of data as below from a column to a line, for example, just copy and right-click to select. paste special then select transpose and you're done.

Excel Tips: 38 extremely useful tips + shortcuts Picture 18

17. Excel trick - Hide all data types

In most jobs that have to manage data by excel, almost security is put on top, above all other issues. As for security and hide all data types in excel such as cells, rows, columns, spreadsheets, . then you can refer to this excellent series of TipsMake: All types of data hiding in Excel here:

18. Excel tips - Enter data starting with 0

This problem often you accounting or general economy will often encounter problems entering the invoice number type 00000004 or 00000…. something. If you enter normally, excel will only display normal numbers such as 4 5 or 345345, but not a leading zero sequence. To fix this problem, you just need to add a 'sign in the front, or right-click on the cell or data range and select format cells - custom - write 8 zeros, for example:

Excel Tips: 38 extremely useful tips + shortcuts Picture 19

19. Convert uppercase and lowercase letters

You can convert from lowercase to uppercase using the = Upper () function as in the example below:

Excel Tips: 38 extremely useful tips + shortcuts Picture 20

20. Excel tips - How to create drop-down lists in excel

In the process of working, accountants have to enter a lot of data, especially those who do warehouse, when entering the detailed sales list, you will have to type each item, so it takes time, sometimes Left many mistakes. Therefore, within the framework of today's article, TipsMake will guide you how to create drop down lists - drop down lists to select data in excel. It will be very convenient, faster and more accurate. Because this trick is quite complicated, TipsMake has written a separate post here:

Part 3: Magical shortcuts in excel

The keyboard, shortcut keys in excel will be an effective tool to help you get rid of the situation of using the keyboard and waving your hands with the mouse pointing cursor, which will help your typing and working speed Excel together greatly increased, and the following are the most useful shortcuts:

21. Excel 3 tip - Automatically sum without using a function with 'ALT + ='

Most Excel users know of the 'SUM' sum function used to sum a range of values, which is really simple. But not everyone knows how to use this function quickly. We really do not need to write out a complete syntax: Sum (number1, number 2, .) but only need to apply either of the following two ways:

- Use the key combination ALT and key = then press Enter -> less than 1s is finished

- Click the command button (command) the total symbol in mathematics on the ribbon bar - Home tab

* Note the total data area must be adjacent, not separated

Excel Tips: 38 extremely useful tips + shortcuts Picture 21

22. Excel P3 trick - Turn on extremely fast Data Filter with 'Ctrl + Shift + L'

As the current work of the author of this article very often uses the Excel filtering function, if you have to just enter data and drag the mouse, it is very tiring. But fortunately we can turn on EXTREMELY data filtering, just about ONE SECOND with the two keystrokes 'Ctrl + A' (Highlight the whole table), and 'Ctrl + Shift + L' (Turn on the filter function). Note that the data table you want to filter must include adjacent or closely linked data cells, so you only need to click on ANY cell in the table, then press 2 groups The other key combination is done.

* You can turn off the filter function at any location also with the same key combination.

23. Excel tips P3 - Display formulas quickly with 'Ctrl + ~'

Those who often struggle with excel formulas cannot help but know this key combination. Instead of having to go to the Formulas tab - Show Formulas, all you need to do is press the key combination 'Ctrl + ~'. And this is the result:

Excel Tips: 38 extremely useful tips + shortcuts Picture 22

If you want to hide the formula, repeat the CTR + ~ key combination (ctrl + `)

24. Excel P3 tip - Move cursor quickly with 'Ctrl + arrow'

This trick will be very helpful when you have to manipulate a huge amount of data and confusion. Instead of holding the mouse and dragging your mouse tired, dizzy, you only need to use the ctrl key in combination with the up and down arrow keys, for example, press 'ctrl + down arrow', then The cursor will go to the end of the last range, similar to the rest of the range.

25. Excel P3 tip - Highlight an adjacent data area with 'CTR + Shift + arrow'

As above tip: navigate quickly with Ctrl + arrow keys up and down left and right. So more advanced we can apply the above key combination to black out a contiguous data area ABOVE, BELOW, LEFT, RIGHT of the cell you are selecting. Try selecting any cell in the middle of your data table and then press 'Ctrl + Shift + navigation arrow keys' to see what happens!

26. Excel P3 tips - Move between sheets with 'Crtl + Pg Up, Pg Dn'

You can quickly move between existing sheets by pressing 'Ctrl + Pg Up' (go to the sheet to the left of the current sheet), 'Ctrl + Pg Dn' (go to the sheet to the right of the current sheet). If your excel file only has a few Sheets, these keystrokes are really great.

27. Excel P3 trick - Move between Workbooks with 'Crtl + Shift + Tab'

If you can 'Move between Worksheets' with 'Crtl + Pg Up, Pg Dn'. So you can also do the same thing with Workbook, this tip will be quite useful for those who have to work multi-tasking, at the same time busy browsing, turning on the word, turning on 2, 3 files, excel workbook, just turn on something else . Try opening several things at once into one of the excel workbooks, press 'Ctrl + Shift + Tab' to see the great effect of it.

28. Trick excel P3 - Show ribbon bar with 'Ctrl + F1'

It won't be a problem if you are sitting in front of large tens of inches of computer screens. But with pretty small computer screens, the Excel workspace will need to be as airy as possible. And that's when the 'Ctrl + F1' key combination shows!

29. Excel P3 trick - display Paste Special with 'Ctrl + Alt + V'

Specialized Excel accountants will not be surprised with the Paste Special feature. With the key combination 'Ctrl + Alt + V', from now on we do not have to right click then select Paste Special anymore. That's one, two seconds less!

30. Excel P3 tip - Move to cell A1 with the key combination 'Ctrl + Home'

It will be a nightmare for anyone who has to work with a DATA PLATE in excel without knowing the quick-key navigation combinations, including this 'Ctrl + Home' key combination. You're at cell A1000000 for example. What do you think if you have to move to cell A1 by using the mouse to drag. Probably enticing to the end of the day too. At that time, try pressing 'Ctrl + Home' and enjoy the paradise of speed!

31. Excel P3 tip - Move to the last cell in a sheet with Ctrl + End

This is also a nice shortcut, but unlike the 'top box', there is a special thing before you use this key combination you have to understand what the 'last box' is. is a really . useless key combination. Suppose now that you are curious to open a completely new excel file completely blank data and press try this key combination, you will find it really . useless episode 2. So what is the last box? That is the intersection of the last row and column of Excel containing some data, which can be understood as the lower right edge of the 'data rectangle' of the excel worksheet that you manipulate. Perhaps by reading this you have understood. Try it and experience it!

* Note this is also a way to check if there are any redundant data boxes that you do not know.

32. Excel P3 tip - Display the next result of your search with Shift F4

This is a pretty good key combination according to the author. For example, you are looking for a phrase 'excel tips' in the spreadsheet for example. You press Ctrl + F then want to search again, you must keep the table showing the search function and click find next, really annoying. And this shortcut combination is the solution for you!

33. Excel P3 tips - Go to special with Ctrl + G and Alt + S

Go to special is a special function in excel, exactly as its 'Special' name. Not only can you go to the cell (A1 to B1 or A1000), but you can also go to the blank cells, existing formulas, and there are countless other good things waiting for you to explore with the set. 2 key combination

34. Excel P3 trick - Create a new line in excel with Alt + Enter

Down a new line as in word, it is quite simple just press Enter is finished. But with excel, you need more than that. And that's when the Alt + Enter key combination shows me

35. Excel P3 tips - Insert current date with Ctrl +;

Instead of having to look down at the clock in the bottom left corner of the screen and type and enter the date in a certain cell in excel, you only take 1 second with Ctrl +; Hey, quick - neat - quick.

36. Excel P3 trick - Insert the current time with Ctrl + Shift +:

Similar to the above key combination, you only need less than a second to insert the current hour and minute with Ctrl + Shift +:. This key combination will be very useful for some people who have to type in time.

37. Excel P3 tips - Scan formulas, copy data down with Ctrl + D

This is also an extremely useful key combination especially for accountants, you will probably not be surprised. Instead of having to drag the formula or copy the data from the top cell to the lower cells, just combine Ctrl + D (Control - Down), with shift + down arrow or Ctrl + Shift + down arrow is done.

38. Excel P3 tips - Format everything in excel with Ctrl + 1

- If you do not know the Format cells function, it will be a huge omission even for those who are new to Excel. Perhaps we are all too familiar with this near-total format feature. But not everyone knows that its keyboard shortcut is 'Ctrl + 1', instead of right-clicking on a cell / area to format -> select formart cells.