Microsoft Excel tricks: Become an expert with these secret tips and advice – List 2022

Microsoft Excel has become one of the most important and useful programs to do everything type of management and projects. This is because it allows its users to perform advanced features that will help you create really good files.

Today there are many companies and projects that are based solely on this program, since it is capable of carrying out a large number of functions able to carry out many businesses. But, the operation of it is not usually as simple and easy as those of other microsoft programs.

Because the complexity of excelhere we want to introduce you the main tricks and tips that will help you become an expert in this tool. a form of power increase all your knowledge and become a true professional with this spreadsheet.

What features make Microsoft Excel a spreadsheet program?

Is spreadsheet comes integrated in the software package microsoft-office. This is how it has stood out for being an ideal software for designing finance projects and documents because it provides a ability to organize digital data. For this, a large number of formulas and shortcuts that will help solve mathematical equations in a matter of seconds.

Bearing this in mind, here we present its main characteristics:

Provides data storage

One of the great advantages that we can find with this software is that makes it easy to store and organize data. This is how each of his work sheets they can contain a large amount of information, which will allow you to store large amounts of data in one placethis being ideal for large companies.

Other advantages that this offers is that it gives you the possibility of create tables with great ease, move and merge cells, create different data tables in the same file, delete cells, among many other functions.

Possibility of completely modifying the stored information

One of the most important aspects of this tool is that it gives you the possibility to modify practically all the content elements in each of these files.

This is how you have the opportunity to change the size and font of the letter, modify the color and style of the letter, highlight cells with colors or different line sizes, increase or decrease cell size, and adjust any type of data stored in these documents.

The use of formulas

It can be said that this is one of the most important factors for the correct and adequate use of Excel. It includes a large number of mathematical formulas that will help you perform different calculations in each of cells, rows or columns. Each of these formulas can be simple or complex depending basically on the operation to execute.

In this way you can start doing quick calculations, averaging, realize addition or subtractionamong many other operations.

Allows you to make diagrams and graphs

It can be said that this is one of the most important functions of this software, as it gives its users the possibility of create charts or diagrams in a very simple way. This allows them to be able visually represent Some results or parameters of some information that is handled within the project. This is how you can start create bar, line, pie charts, among many others.

What types of documents can I create or edit in Excel?

As we mentioned before, this type of program is mostly used by companies and independent workers to carry out each Projects. And it is that today these applications are used to perform a large number of data logs that can be store in one place.

This is how this tool is currently used to create or edit different types of documents which we mention below:

administrative documents

These administrative functions are created and stored as filesthey can contain a large amount of information that can become edited, formatted or simply saved in the cloud or on a computer. This is how in them you can start writing different note sheets, fax covers, brochures, newsletters, notice, graphs, chartsamong many others.

Create Reports

In the case of this application you have two ways to create the reportsEither for a company or individually. This is how it can be used mainly to start create supporting data for a written reportthis could eventually include things like a data range, tables, graphsand many more.

It can also be said that these types of tools are essential when it comes to sales reportswhere you can create a record according to customers, vendors, products used and also be able to track each of these factors.

Therefore, all this has led him to become one of the programs preferred by most companies to design your financial summaries, personal vacation documents, employee illness records, and compliance documents.

research papers

It can be said that this is one of the factors where this software makes a greater representation, since most of merchants and businessmen they perform different investigations of possible business models for their businesses. This is how they end creating comparative structures Come in the list of prices, costs of the merchandise, cost of production and holiday periods.

In this case Excel gives you the possibility of store and calculate each of these data. In addition, it allows you to use different elements so that you can see all these comparatives visually either through a chart or diagram. It also gives you the possibility to store everything in a single book or everything independently.

Data storage documents

In the case of this microsoft app you can use different spreadsheets where you can start store numeric data in a format of rows or columns. Ideal for create a data analysis, encrypt the amount of sales, know exactly the sales taxes, price increases or commissions.

In addition, it gives you the opportunity to convert any worksheet to a chart of different forms of representation, whether in circles, bar, linesor simply in a visual diagram.

List of the best 50 Excel tricks to get the most out of it

Currently the use of this software has become a fundamental part of small, medium and large companiesas well as for independent workers. Since through it you can carry out endless functions that are completely important for the development of any type of project.

As mentioned above, it works mostly through formulas and different shortcuts that will help to perform functions in a faster and easier way, this being something fundamental for its proper development.

Bearing all this in mind, here we are going to present a series of tricks so you can start using it and you can improve your experience in him becoming everything an expert in this area.

To do this, here we show you the 50 best Excel tricks:

Select all content with a single click

When working with this type of files it is important to have a tool that allows us to select all content stored in it to perform a specific action. Surely you already know the key combination “Ctrl + A” which will help you do this.

However, there is an even easier way to do all this, to do this use the top right corner button to select all sheet data in just one second.

Customize your shortcut menu

In the case of this program the toolbar you have the opportunity to find only three kinds of shortcuts with quick access. In general, they are usually in it “Save”, “Undo” and “Redo”.

However, as you can see only three can be very few shortcuts available, in case we want to add other shortcuts to this bar we can do it as follows:

For this we enter “File, Archive” and then we select “Options” and there we click on “Quick Access Toolbar” In this box you can start adding the options you prefer, either as “Copy paste”, “Delete cell”, among other.

Copy and move data faster

One of the most important aspects when working with a project or data report it’s how fast we do it. For this, it is important to know what tools or methods they will allow us save time in a few steps.

So if you want to move any data column within sheetone of the fastest ways to do it is moving the cursor to the edge of that column until he appears with four arrows in different directions, here you can move it to the sides you want. in case you want copy the data simply press the “Ctrl” key before dragging the column.

Insert a column or several rows

Can insert a column or a row It is a very simple task to do and that you probably already know, but insert single row when actually several are needed, it turns out to be a waste of timesince it would touch repeat the procedure several times. In these cases, there is a way to add the rows you want in one step.

In these cases it is drag the cursor and select the number of rows or columns you want to add. Once they are selected, the next thing will be to do right click and choose the option “Insert”, then in “New rows” we select if we want it to be add down, left or right according to the case.

Browse different files at once

One of the hardest things about work in Excel it is when different templates are being handled at the same time, since it is a bit tedious to have to be entering one and the other to write down some information in each one of them. Furthermore, any carelessness in the transcript of some data can affect work.

Therefore, the best way to work this is to use the key combination “Ctrl + Tab”, since it will allow you to be able to work between each of the files in a very easy way, thus avoiding any type of error.

Delete blank cells

On many occasions when we already have a part of the report developed we can get some completely blank cells in places where they shouldn’t be. It is important to note that this can seriously affect if you want to specify some calculationsespecially when it comes to getting a average.

Being able to eliminate it is a simple process, for this you must filtersr, in this case the best is select column where is it located the blank cell and then click on the tab “Data”, there we select “Filter” and then a down arrow will appear.

Here we uncheck the box “Select all” and we select the last one as the option of “empty” Once this is done, all those that are completely blank will disappear. Lastly, go to “Start” and click “Erase”.

Copy unique records

On many occasions you need to copy only those unique records, that is to say that it does not have any type of repetitions. All this can be achieved through “Advanced Filters” a function that very few know. In this case, the entire column must be selected and then we enter “Data” > “Advanced”.

Here a new window will open where we select “Copy somewhere else” and then we select the place in the template where we want copy the unique values. We can add these either by entering the values ​​or by the icon to select the areas. This will allow you copy the elements from tab C to Dfor instance.

Advanced search with wildcards

Commonly when we want to make a advanced search we use the key combination “Ctrl + B” for it. However, there are two characters that we can use as wildcards to carry out this function, it is about sign of question mark and asterisk.

In the case of the question mark “?” is especially used for when we are not sure what the result of the search what we are doing. That is, you can enter “ca?a”, and you can get results like “Box”, “Bed”, “House”, among others. If we use the “*” is about replace multiple charactersthat is, we can write “pa*a” and you can get results like “departure”, “word”, among other results.

Insert a diagonal line in a cell

when we are creating a list on many occasions we need some diagonal That in separate the text Of the same. In these cases you should go to the tab of “Start”, there select “edges” and then “More Edges”, this is where you can find one diagonal line to add to the cell. Just select it and “Save”.

Restrict some values

In many cases it is recommended restrict some values ​​and set some parameters for the entry of information, this will allow you to have all the information that you include in the form is completely valid. That is, the numbers entered are only whole numbers and no decimals. This will help you make sure that this is the case and you do not have any kind of problem with it.

To do this, you must go to “Data” > “Data validation” > “Settings” and there you enter the conditions you want to give, you can select parameter of the same, if it is about the age and a group of person you can choose between 20 years minimum and 30 years maximum.

Move data from one row to one column

Sometimes you can work some queued information and after you have everything ready you want to see all this in column.

For this there is a way to do it easily instead of having to write everything again. In this case it is about select the text you want to transport and copy it with “Ctrl + C”then select the blank cell where you want to place it, there you go to “Home” > “Paste” > “Transpose”this way you can perform this action.

Quickly navigate with “Ctrl + arrow key”

One way to get moving the edges of the sheet quickly is by using the combination of “Ctrl + arrow key”, in this case you must press the one that has the direction you want to go. This will allow you move him to the end of the row in a matter of seconds.

Use the & symbol to typeset any type of text

East symbol “&” will help you to compose any type of text without problem, you don’t have to use any type of formula that complicates your life. you can compose two cells, three cells or all those you want so that they appear to you in one.

In order to carry out this function you must go to the cell where you want the information to appear, use this formula with the number and letter of the box you want to compose “A1&B1&C1” and click “Enter”.

Make text uppercase or lowercase

How many times has it not happened to you that you have write some text and then you realized that everything must be in uppercase or lowercase. There is a way to convert all this without the need for write it again everything the right way.

Hide some data

Generally, most users use the classic method to hide the data They don’t want you to be present. To do this, just right click on the cell and choose the option “Disguise”this process can be acceptable when it comes to few elements that we want to hide in the template.

In the event that you want to hide a large number of elements, it is necessary to use another tool to carry out this function, for this we are going to “Cell Format” > “Number” > “Custom” > “Type “;;;” > “OK”. After doing this there will be hidden all those values that are included in the area you have entered.

Enter all those values ​​that start with “0”

One of the biggest problems when looking for some values ​​is when it starts with the “0”, since the system will automatically remove it by default. However, a very simple way to correct this inconvenience is by adding a (‘) before 0. This will prevent it from being deleted.

Modify the name of the sheet to your liking

On many occasions we want to work with increased comfortand this includes many times the names of the documents we work with. Therefore, we can rename simply by double clicking on the name of the sheet and writing the one we want to place.

Get more values ​​with a single click

The most normal to get a addition, an average or a subtraction is by selecting the column and scrolling down. However, what you didn’t know is that you can get many more values ​​just by right clicking on it.

Use the automatic corrector

On some occasions when we are filling in a table we must enter the same data several timesthis can become tedious for most or it can just produce a lot of mistakes when typing it several times.

In these cases it is recommended to use the automatic corrector, in it you simply have to replace that word with an easier one. That is, if you have to write Albert Gonzales you can replace it with AG.

For to activate this you must click on “File” > “Options” > “Review” > “AutoCorrect Options” and here you enter the text you want to change in the section “Replace”.

Open multiple Excel files simultaneously

Many times you have to work with different files at the same time, this requires you to open one by one what you can become a time-consuming process and that on many occasions we do not have it.

For this power open them all with one step. Select all the items you want to see and press the enter keyin a matter of seconds they will all open on your screen.

Modify cell formatting with the help of shortcuts

On many occasions we want change the format of a cell specifically for various reasons. This is how you can change this format to number with style of percentage thousands coinsamong many others.

To do this you can use one of the following key combinations:

• Ctrl + Shift + 1 = Thousands style.
• Ctrl + Shift + 4 = Coin style.
• Ctrl + Shift + 5 = Percent style.
• Ctrl + Shift + 7 = Scientific style.

Insert a new sheet in the workbook

you may find yourself working on the workbook and you need to include a new sheet in it. In these cases you can do it in a very simple and fast way. You just have to press the keys “Shift + F11” and automatically you can add a new sheet in your workbook and continue with your work without wasting much time.

Move between the different sheets

When we are working on a book with different sheets it is important to be quite agile to not make mistakes and to be able to work very quickly. In this case we are going to teach a trick So you can switch to the next sheet in a matter of seconds, To do this, simply press the following key combination: “Ctrl + Page Down”.

Launches the Find dialog

One of the most used options when working on these books is the option of “Search”, one way to access it quickly is by pressing the keys “Ctrl + B” simultaneously.

Open the paste special data box

You may need to do a special glue while you’re working on one of the templates. One way to access this box is through the following keys “Ctrl + Alt + V”, here you can select the type of special glue what you need and finally click on “To accept”.

Add a border to your selection

In case you have made a selection of your information and want add a border to differentiate it from the rest, you can do it in a very simple and fast way. To do this you must use the following key combination: “Ctrl + Shift + &”, this will allow you to create a edge directly to the area you have selected.

Underline the content of a cell

you may want underline a specific cell either because it contains a important information that should stand out from the others or simply to locate important information in a quick and easy way. In these cases it is necessary that you select said cell and press the following buttons on your keyboard: “Ctrl + S”.

Repeat the content of the cell above

This trick is ideal for when we have to put some data and we have some of them the same, in these cases we must use the following combination of buttons “Ctrl + ‘” it will give you the possibility of repeat the content that is written in the top cell.

Insert a comment in one of the cells

On many occasions we need insert a comment in any of the cells for some special reason, for this we must press the keys “Shift + F2”. It is important to note that this method is known to very few users of this software.

Double click to copy a formula

If you’re looking for a way to copy a formula throughout its set, It is not necessary to select the first data and you have to drag it with the mouse to the end of the column. you can just do double click on the bottom corner of the cell and automatically the other values ​​will appear.

Select the data in blocks

On many occasions we want select a block complete for copy the values that are there. This can usually be done with the course by selecting all the bar, but, there is an easier way to do this with your keyboard. In this case you must press the buttons “Ctrl + Shift + arrow” you must press the direction of the arrow where you want to select the block.

Change values ​​quickly

Maybe you didn’t know you can change some values very quickly, either for place number with two decimals, to convert them into dollars or to take them to percentage. All this you can do through some key combinations that we will show you next:

• Numbers with two decimals: Ctrl + Shift + !.
• To convert them to percentage: Ctrl+Shift%.
• To convert them to dollars: Ctrl + Shift + $.

Blocking the cells with the F4 key

This tool is very important to use when we have noted any formula that we do not want for any reason to move, be changed or deleted. To do this, you just have to place the cursor on the value and press the F4 key for it to be blocked.

Keep in mind that if you press it 1 time it will be cell Lock, if you keep pressing it it will go through row number lock, column letter lock and finally, no lock.

Calculate all operations

Mostly when working with calculation reports it is necessary to carry out a lot of number of operations. Therefore, all this ends up taking a lot of time to start developing one by one. For this you can use a key that will help you calculate all the operations of your worksheetit’s about pressing the F9 button and automatically all pending operations will be performed.

Calculate a specific operation

you may have different pending operations in your work template, but you only want to develop a single one for now. In this case the same button is used as in the previous trick, but we add one more part to it. That is, we must press “F9 + selection of part of a formula”. This will help you get the result of only the equation you have selected.

Calculate only the operations of the current sheet

If you are working with different sheets in your book, and you just want to solve the single Equations, then you can start using this button combination “Shift + F9”, this will give you the possibility to solve all the operations of the template current job.

Add the current date to one of the boxes

you may need to know the current date or do you just have to add it to your workto do this you must place the cursor in one of the boxes and then press the buttons “ctrl + ;” it will automatically appear the day, the month and the year.

Select the entire row and column of the cell

You may need to select only the column where you are located either to copy it and apply a border type to it. In this case you can do it using “Ctrl + Space bar”, this will save you from having to use your cursor and select all of it.

Bold the content of the box

In most cases start coltouch the bold in the boxes corresponding can be a somewhat tedious and time-consuming process. In this case you can do it in a more agile way using this combination “Ctrl + N”.

Put the values ​​in time format

If you are needing to pass some specific data to the time formatthen you can do it simply by pressing the keys “Ctrl + Shift + @”.

Summarize the data

For this function it is necessary to use the “Count Yes” and “Add.if”. for the function of “Count Yes” the count of the number of times that will appear a value in the range that has been selected. There are two types of input here, the first is the number of values ​​you want to count and the second is the criteria you are looking for.

As for the function of “Add if” all will be added the values ​​of the selected range, here we will find the area, the area of ​​the sum and the criteria.

Use the Findv function

This tool gives you the possibility to search for any value type within a selected range. Ideal for not wasting time when you want to look for something special item. In case you have a large amount of number, You only have to enter the one you are looking for so that it is thrown in the result.

Generate random values

The function of random values takes care of return a random real number greater than or equal to 0 and less than 1, that they are evenly distributed. Therefore, every time that function is applied in the work template a will be returned new random real number.

add new rows

If you are looking for a way to add new rows between existing rowsthen you will have to use the following buttons “Ctrl + +”. This gives you the possibility of continuing to work in a comfortable way, bearing in mind that this type of action can be complicated if you do not know the right tools to perform them.

Flash Fill

This option will allow you automatically fill in your data if you have previously detected a pattern to follow. Note that for this the option of Flash Fill must be activated, for this you go to “File Options > Advanced”.

There you can start set a pattern as for example 00001 in the first blank cell. Now start to write the following number in the box below and automatically Flash Fill will recognize the pattern and it will fill in all the numbers for you.

The Index and Match functions

Both the Index functions and match are one of the most used tools in this program to perform the different searches. Furthermore, both can be combined to extract the elements you need. This is how these two functions allow you to look up a value anywhere in the lookup table.

In other words, no matter how much information you may be handling within the worksheethere the entire table will be searched until a accurate result.

Clear the information in the box and exit it

sometimes it is necessary delete some of the information written in some of the boxes of the table, doing this with the help of the mouse is possible but the procedure will be longer and slower. For this we recommend using the ESC and Delete button on your keyboard, You just have to select the box and press the delete button and then ESCautomatically will remove the text and exit the box.

Return to cell A1

You may be working with a very large content and need to return to the start of the project, you can do this with the help of the mouse moving the template bars, but this can take up a lot of your time if there is a lot of content stored there. The best way to do this is by pressing the buttons “Ctrl + Home” and you will automatically return to box A1 of the document.

Go to the last box of the document

Now the opposite happens to the previous case, we have a very extensive content in the document and we meet in the beginning of it and we have to go to the last box. In this case we can use the key combination “Ctrl + delete” to be able to go to the last active cell in a matter of seconds.

Create a chart in one step

To finish this list we show you this form of power create a graph on the table with just two keys. This is ideal for when we have written some kind of table with numerical data that needs to be represented visually. For this it will be necessary to use the keys “Alt + F1”, here automatically It will be created a graphic across the center of the sheet.