software

How to treat and work the data in an excel spreadsheet? Step by step guide

To process and work with the data in an Excel sheet, you will need to know what types of documents you can use the Microsoft calculation template .

Due to this, we have prepared this article so that you can find all the information related to the step by step that you will do to work with the information that you enter in a calculation template.

If you want to know more, you will have to continue reading. We will explain what a formula is and how you can use it in the best way. Finally, we will also tell you what the movements through the cells are and how you should use the views.

What kind of documents can be worked on in Excel?

The types of documents that you can work with in Microsoft Excel are the following:

  • Symbolic link files .
  • Database of type DBF 3 and DBF 4 .
  • Data exchange documents .
  • Text files in DOS .
  • Documents in XPS format .
  • Microsoft Excel file formats .
  • OpenDocument spreadsheet .
  • Pictures .
  • Bitmap .
  • Office drawing object in EMF format .
  • OwnerLink, ObjectLink, GIFs, and JPG files .
  • PDF .
  • Display text .
  • Unicode text .
  • Values ​​delimited by commas, tabs, or spaces.

Learn step by step how to treat the data in your Excel spreadsheet

Next, we will explain the step by step you must do to process the data in your Excel spreadsheet:

Formulas

Microsoft Excel is characterized by having a large number of formulas that allow you to perform tasks in just a few steps. In this way data can be processed and accurate results can be obtained on different topics. To access the formulas you will have to enter the tab that bears that name and look for the group of tools, Library of functions.

Section you will find the following formulas: 

  • When you use this tool, Excel automatically detects the cells that are above the one you positioned the cursor and gives you the sum of them as a result. It is ideal when you have to perform this arithmetic operation with many addends.
  • In this section you will be able to find all the formulas that are related to financial mathematics . With these you will be able to know the internal rate of return, net present value, present value, future value and rate of return, among other elements.
  • Logic. This type of formulas can be used every time you want to interpret and control spreadsheets in which you want to establish a logical value according to the parameters you indicate.
  • These types of formulas are used when you work with text data and you want to repeat the same, find a variable or move it to a certain place. There is also the CURRENCY function , with which you can transform a text into a currency number.
  • Date and Time. Every time you need to set the number of business days, indicate a date or know the days of the week, you must use this group of functions. It is ideal for making accounting tables and personnel control .
  • Search and reference. They are widely used formulas every time you want to work with a database or a table and you need to extract a certain variable on another sheet or in another sector of the same sheet. It is used frequently when controls are carried out on suppliers, since the information of the seller of the product can be extracted by simply entering a piece of information.
  • Mathematics and trigonometric. In these formulas you will find functions that are related to logarithms, polynomials and areas, among other concepts.
  • Other functions. Excel groups statistical, engineering and compatibility formulas, among others, in this section. So every time you want to find a median, an average or a percentile you will have to use these tools .

Adding data

When you need to add data you can do it directly through the AUTOSUMA function , for this you will have to select all the cells you want to add and then press Enter.

Another way you have using the keyboard is to type the = sign and then enter the first words of the formula, Microsoft Excel will help you with the autocompletion and will show you the results in a window that you will find next to the cell you type. To access the function you want, you can do it using the directional arrows and then press Enter.

Movement through cells

Moving around the cells will allow you to know what formulas each one has and what their syntax is to understand the origin of the data you want to analyze . To do this you will have to use the address dates on the keyboard or use the mouse to select the desired cell. If you want to see which formula the cell has, you can press the F2 key or double-click  to find out the origin of each field in the same spreadsheet.

Insert shapes

When you want your spreadsheet to include graphs that are related to block drawings, flow chart stars or lines, you can use the Forms function that Microsoft Excel offers you . To do this, you will have to go to the Insert tab , then click on Illustrations and finally select Shapes . Next, you will have to go to the place where you want the graph to appear and press Enter .

If you wish, you can modify the contour lines and the fill, for this you will have to right click on the shape and then choose the Format shape option . So that a configuration panel appears on the screen in which you can choose the fill and the type of lines. You will also be able to incorporate and edit words if you choose the Text Options tab . This will allow you to generate text effects or include boxes within the graphic figure.

When you have several drawings in your template, you can sort them by moving them forward or backward if you press the right mouse button and choose the Bring to front or Send to back option . In case you want to group them to move them all together so that you can copy them and modify their size, you will have to work with the Group option once you have selected all of them while holding down the Ctrl key .

Cell autocomplete

The cell autocomplete tool allows you to quickly and accurately find the formula you want. For this you will have to insert, in the cell in which you want Excel to give you the result, the = sign and then start writing the name of the formula .

You will see a pop-up window that will be located next to the cell you are typing in. In this table you will find all the suggestions that the program gives you so that you can choose the correct formula. When you find the one you need, you will have to select it with the mouse or with a directional arrow on the keyboard.

Add or remove rows or columns

When you want to add rows or columns you can do it completely or incorporate or delete certain cells. That is, it will not be necessary to incorporate an entire horizontal or vertical line.

In the first case you will have to select the column or row and then right click. Next, you will have to choose the Insert option (or Delete, as the case may be) to finish with your goal. In the event that you need to add or remove certain cells, you will have to select them and right-click to choose Insert or Delete .

Next, you will have to choose:

  • Shift cells to the right .
  • Shift cells to down .
  • Insert a whole row .
  • Insert a whole column .

You must bear in mind that this movement can affect the format of the rest of the data table, especially if you have merged cells.

Hide rows or columns

If you have a spreadsheet that has auxiliary calculations that allow you to better analyze the information, you will be able to hide these reinforcement operations so that they do not appear in the printout of the sheet nor so that they generate a visual complication on your screen. You can hide both cells and rows completely .

In this case, you must select the line you want to stop seeing by clicking on the header of the row or column. Then, you will have to right click and select Hide or Show (depending on your case). A quick way you have to choose what you want is to do it through the keyboard. For a column you will have to press the Ctrl keys and the space bar together . If you want to select a row, you are going to use the spacebar + Shift shortcut .

Wrap text in cells

If you need to accommodate text in the cell or a combination of these, you will have to use the Wrap Text function . This will allow you to find all the words that you have written inside the cell, but you must bear in mind that the height of it will change.

To perform this task you will have to right click on the cell you want to have sorted and then choose the Format cells option . A new window will open in which you will have to choose the Alignment tab and enable, in the Text Control field , the Wrap Text tool . You can also perform these steps by going to the Home tab and looking in the Alignment group for the Wrap Text feature . This is very useful when you are working with tables and you need to sort the column or row headings.

Text format

The text format in Microsoft Excel refers to the type of data that will appear in the cells and with which you will work as if they were words and not as dates, numbers or currencies . This has the advantage that Excel will not add or average this data, among other functions.

In addition, you can use logical formulas that will help you find different variables more easily . To convert a cell to a text format, you will have to select it and right-click . Then, you will have to choose the Format cells option and choose the Number tab . When you are in this place, look in the Category list for the Text function .

Absolute references

An absolute reference is one that is always related to the same cell (or range of these) and allows the formula to remain unchanged when it includes that cell. In this way, you will be able to move the absolute reference cell to either side of the calculation template and you will be sure that the result will not be altered.

To make an absolute reference you will have to create the formula with the corresponding syntax and then enter the $ symbol in front of the column and row names . For example, if you have cell D4, you can make it an absolute reference $ D $ 4 . This is very useful when you want a formula that you have created to encompass other cells, but if you drag it with the mouse, Microsoft Excel will take it as a mobile reference. For this reason, it is a good idea to use the absolute reference.

Views

This tool refers to how you can see the Excel template you are working with on your screen . You can choose a normal view, in which you will have the classic view offered by the program or choose a page break view, a view of how the document will look when it is printed . You will also be able to create custom views .

To make these modifications, you will have to go to the View tab and choose the Book Views group and thus you will find all the available alternatives. If you prefer, you can remove the grid lines, the formula bar and the headings .

Cell format

If you select a cell and right-click you can choose the Format Cells option . In this way, you will access different tabs that will help you configure the data that you will include in the cell .

The tabs are:

  • Number. You will be able to modify the type of format that the cell will have, so you can choose if you want a general format, number, currency, accounting and date, among other options.
  • Alignment. This tab can be used to align the text in a horizontal and vertical location . You will also be able to use to fit the text to a cell, combine them or reduce a sentence to the size of an area . Finally, you will have the option to choose a direction of the text, that is, if you want to do it from the right to the left or vice versa.
  • This tab will allow you to configure the font that will appear in the calculation template . You will be able to choose the font type, style, size and color.
  • If you wish, the cell or range you have chosen may have a border or outline, so you can choose its color and thickness.
  • As with the border, you will also be able to include a certain color and fill effect in the cells .
  • This section is used to lock cells and hide formulas so that other users cannot modify them.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button