Microsoft Excel is an electronic spreadsheet or tabulator, it is a program that allows you to perform simple, arithmetic, statistical, financial, etc. calculations, with a series of previously entered data. With Excel we can generate charts with the help of your assistant. Excel has a large number of functions that facilitate the calculation of complex operations.
basic-concepts-excel-beginners1.1 Enter the application:
Default extension:.xlsx
It is usually looked for in the START button in case it appears in the list of recently executed elements.
When installed it is always located in:
Click START button \ Mouse over ALL PROGRAMS \ in the area that opens go to the Microsoft Office Folder and click on the Excel icon.
When the PROGRAM is activated, the window that represents it opens.
1.2. Basic concepts.
- When Excel opens, it presents a Workbook: it allows you to store, visualize and manipulate the data, which is made up of Spreadsheets, which is the name of each work area. Each sheet has a name (Sheet1, Sheet2,…, Sheet255 = 256 sheets), by default it opens with three sheets, although this value can be changed. The work area consists of:
o Columns, a letter is assigned and they are arranged vertically, the Column Headers go from A to XFD for a total of 16 384 columns.
o Rows, it is assigned a number that appears to the left of the window arranged horizontally, the Rows Header goes from 1 to 1,048,576 (this number varies according to the package version
Office).
o The rows and columns form pages, therefore, we can say that a sheet has approximately 48,507 pages, this number can vary.
o The union of a column with a row is called Cell, for example A1, G8, AA32, LVB48, here numerical, text, date, time, formulas, and functions data are stored.
o The selection of several cells is called Range, a range of cells for example, that goes from A1, A2, A3, A4 and A5 is represented by the following name: A1: A5, another from B1, B2, B3, B4, C1, C2, C3, C4 will be B1: C4, the name of a range will always be the reference to the first and last cell selected and separated by a colon (:).
1.2.1. Application window. Her parts.
1.2.2. Work area. Her parts.
1.2.3. Mouse shapes.
(Always dragging the mouse)
--– with these MOUSE forms
we can select groups of columns or rows (one group at a time) by DRAGING from the first to the last.
- with these forms of the MOUSE
we can enlarge or shrink groups of columns or rows (one group at a time)
DRAGING from the first to the last.
Explained in: Practice 1 and Practice 2.
Mouse in the ACTIVE cell: (Always dragging the mouse)
- A whiter: inside or outside the active cell, ALLOWS SELECTING groups of cells.A more black: only in the lower right part of the active cell, ALLOWS TO FILL the desired cell (we call it REPLICATE). Point with 4 arrows: in anywhere on the edge of the active area, ALLOWS MOVING the selected content.
1.2.3. Active Cell and Range.
The ACTIVE CELL is surrounded by a thicker black box than the rest of the cells.
RANK is a group of selected cells.
For example:
It is B2, if column B and row 2 are fixed well, they are marked with a different color (yellow-orange)
RANK
Column range, goes from B3: B13, notice the color change of the active cells.
Row range, goes from B3: F3, notice the color change of the active cells.
Continuous range of cell groups, goes from B3: E10, notice the color change of the active cells.
Discontinuous range of cell groups, ranging from B3: E10; G3: G10; I3: I10 note the color change of the active cells.
The selection of this range is made:
- Select the continuous range from B3: E10. With Ctrl pressed (held), drag the mouse over cells G3: G10 and over I3: I10.
Practice # 1 Modify column width.
Form # 1:
- Mark the columns involved (only by the header) 2. Inside the column name, the ones marked:
For example: look closely at the shape the MOUSE takes, black arrow
() pointed downwards means you can select a group of columns. (REMEMBER - DRAGING (pressing the left button of the MOUSE, releasing when you reach the end)
- By one of the dividing lines of the selected group.
For example: look closely at the form the MOUSE takes, () means that you can enlarge or shrink a group of columns. (REMEMBER - DRAGGING (by pressing the left button of the MOUSE, releasing when you get to the end.) When you do it by a dividing line, it is made to the group of selected columns.
Practice # 2 Modify row height.
Form # 1:
- Mark the rows involved (only by the header) 2. Within the name of the rows, those marked:
For example: look closely at the shape the MOUSE takes, black arrow) pointed to the right means you can select a group of rows. (REMEMBER - DRAGING (pressing the left button of the MOUSE, releasing when you reach the end)
- By one of the dividing lines of the selected group.
For example: look closely at the form the MOUSE takes, () means that you can enlarge or shrink a group of rows. (REMEMBER - DRAGGING (by pressing the left button of the MOUSE, releasing when you reach the end.) When you do it by a dividing line, it is made to the group of selected rows.
the default row number, fig. 2, shows how it varies with changing height.
1.2.4. Scroll buttons between sheets.
Practice # 3 Move to activate sheets. For example: a book with 15 Sheets.
- Click on the label (name) of Sheet2. Click on the label of Sheet14, as it is not visible we click on the button so many times
scroll to the next sheet (third button) until Sheet14 appears, BUT if we only have 15 Sheets we click on the last button (scroll to the last sheet) and click on Sheet 14.
- To return to Sheet1, we click on the first button and click on Sheet1
Summarizing:
The MOUSE takes 7 forms that will help you work in Excel.
To select to replicate (only at the bottom right of the selected area) to move the content of the selected area (anywhere on the edges of the selection).
select by the COLUMN Header.
enlarge or shrink by the COLUMN Header.
select by the FILA Header.
enlarge or shrink by FILA Header.
1.4. I work with the leaves.
In the area of the sheets, right-click on the tab or label or name of one of them, then the following Contextual Menu appears:
NOTE: We can have groups of sheets selected and also use the commands shown.
Before we must know how:
Practice # 4 Select or Mark.
- There are several ways: for example we have a 10-sheet book.
- Select Continuous sheets:
- Click on the first sheet to mark. Press the Shitf key, without releasing it, click on the last sheet to mark.
NOTE: the order of the factors does not alter the product.
For example: click on Sheet3, press Shift and click on Sheet5 or vice versa. They are shaded white, this means that they are all active leaves.
- Select sheets Indistinctly:
- Click on the first sheet to mark.Press the Ctrl key, without releasing it, click on each of the sheets to mark, but to use this form, they must be separated from each other.
For example: Click on Sheet1, Press Ctrl., Click on Sheet3, Sheet7, Sheet9.
We can combine the first two ways:
For example: we click on Sheet3, press Shift and click on Sheet6, press Ctrl and click on Sheet8 and Sheet 10.
- Select ALL:
- Right click on the name or label of any Sheet. In the contextual menu that is activated, click on the Select all sheets option.
NOTE: To unmark them: if there are unselected sheets in the book, the click to unmark must be on a sheet that is not in the selection. But if they are all selected, the click is on anyone.
EYE: Another way to uncheck the group of selected sheets: To deselect multiple sheets, right-click the label of a selected sheet. Then click Ungroup Sheets on the context menu.
IMPORTANT: when selected or checked you will be able to move, delete, hide, change the color of the label, configure them and something VERY IMPORTANT when you work in one, you work in the entire group.
Practice # 5 Insert.
Allows you to add sheets to the book (up to 255 sheets)
Form # 1:
Click on the button that appears at the end of the sheet labels area. (With this form it is inserted only one by one)
Form # 2:
- Home Tab / Insert button of the Cells Ribbon: click on the arrowhead that accompanies it, then the following Context Menu appears:
You choose the last option Insert sheet.
Form # 3:
- Right click on the name of a sheet. In the Context Menu that is displayed click on the Insert option. In the dialog box that is displayed click on the Spreadsheet icon and click on the OK button.
NOTE: you can insert x amount of sheets, for this you must mark a group of them (ALWAYS continuous) and normally follow the steps described in form three.
Practice # 6 Eliminate.
Allows you to remove unnecessary sheets.
- Right-click on the sheet (s) to delete. Click on the Delete command in the context menu that is displayed. • Click on the OK button to confirm the deletion.
Another way:
- Home tab / Delete button / Ribbon Cells: click on the arrowhead that accompanies the command, the area opens with different options;
You choose the last option in this case.
Practice # 7 Rename or Rename.
Lets you type a new name for the Sheet in question.
- Double-click the label on Sheet1 and when it is shaded, type in the name directly. For example: Listing, Enter to confirm.
Staying
Another way:
- Right-click on the sheet label. In the box that opens, click on the Rename command, type the new name and press Enter.
Practice # 8 Move or copy.
It allows you to make an exact copy of the sheet in question and move it to the end if necessary.
Form # 1:
- Right-click on the label of the sheet in question. In the Contextual Menu that is displayed, click on the Move or copy… command. The following dialog box appears and there:
Click on the options (move to the end) and Create a copy
like the copy
I can also just use the Create a copy option, then it will put it in front of the original.
Form # 2:
- Holding the Ctrl key down (held down), drag the tab of the sheet to copy
to another position (the black arrowhead indicates the position to release it on arrival). The copy will show the same name with a 2 in parentheses, then you can rename it (you HAVE the exact copy).
Practice # 9 Protect sheet.
To prevent a user from accidentally or intentionally changing, moving or deleting important data, you can protect certain elements of the spreadsheet, the whole sheet or the workbook with a password (password: way to restrict access. In Excel they can have up to 255 letters, numbers, spaces, and symbols. You must spell capital and lowercase letters correctly when defining and specifying passwords.).
There are different ways:
Form # 1:
- Right click on the name of the sheet. The context menu is activated and we click on the option Protect sheet… The dialog box like the one shown is activated, here the password is entered (when the user is not familiar with MS Excel you must not modify ANYTHING).
when you click the OK or ENTER button. A new dialog box is activated that allows you to confirm the password, click on the OK or ENTER button.
Form # 2:
- Clicking on the Review Tab and clicking on the ALL option is the same as step three of Form # 1.
Practice # 9-1 Protect cells inside a sheet.
It allows to protect only CELLS where mathematical operations and / or functions intervene.
- Mark the cells that you do NOT want to protect (which the user can modify the content). Click on the Font, Alignment or Number ribbon and in the dialog box that opens click on the Protect tab or ear. Click on the locked tab to remove or disable this option (remove the popcorn).Continue with us IDEM with Form # 2.
Practice # 10 Label color.
Allows you to change the background color to the Sheet names area.
- Mark the sheet (s). Right click on the names area and in the area that opens go to the Label color option and in the colors area to be displayed click on the desired one. NOTE: the option without color allows to remove it.
Practice # 11 Hide and Show.
Lets you hide the selected sheets.
- Right-click in the sheet area. In the contextual menu that is displayed, click on the Hide command.
Show a hidden sheet (one at a time).
- Right click in the sheets area. In the contextual menu that is displayed click on the Show command. The dialog box that shows all the hidden pages is activated, click on one and click on the OK button.
Practice # 12 Move.
It allows to order the information that each sheet can contain,
- Left-click on the sheet (or sheets) you need to move and drag directly to the position where you want it to appear. The mouse pointer takes the shape of a leaf and moves it until you release the button in position. The black arrowhead pointing downwards guides you between the sheets, when you release the mouse button, it will be positioned.
Practice # 13 Copy and Paste
When you Copy and Paste DUPLICAS.
- Select what you are going to duplicate (if it is ALL, click on the button that is to the left of COLUMN A and above ROW 1.
or just Ctrl E.
- Click on the Copy command or Ctrl C. Click on the new Sheet. With CELL A1 as ACTIVE (only she) give Paste or Ctrl V.
NOTE: There are times when this form DOES NOT WORK correctly and the copy does not have the desired format, that is why I ALWAYS use the form of Practice # 8. Move or copy…
Practice # 14 To insert or delete rows or columns.
Form # 1:
Insert rows or columns:
- They are marked by clicking on the header as appropriate. Ctrl + sign
Delete rows or columns:
- They are marked by clicking on the header as appropriate. Ctrl sign -
Form # 2:
Another way: by activating the row or column to insert, in the Home / Insert command tab, you choose, by clicking on the arrowhead that accompanies the button, the corresponding option.
Fig. shows the different options.
1.4.1. Change the number of sheets to the new book:
A book when opened has 3 sheets (default) BUT the user does need 18 sheets, inserting is not the same as changing the default amount. (THE BOOK MUST BE COMPLETE BLANK)
- Click on the Start Button. In the area that opens, click on the Excel Options button. The dialog box opens and in the Most frequent option, go to the option When creating new books and there go to Include this number of sheets (up arrow increases, down arrow decreases), the desired quantity is chosen. Remember up to 256.Click on the OK button
NOTE: REMEMBER to close that book and open a new one. These steps are used to start working with a new project, you should not have NADAAAAAAAAA in the book you opened to change the number of LEAVES so CLOSE that and open a NEW BOOK.
1.5. Data entry.
Data in Excel is typed independently into cells.
1.5.1. Text values.
They are texts that are typed in the cell and are automatically aligned to the right of the cell BUT the user can configure it to their needs. For example:
IMPORTANT: the image shows the same numbers in column B and in column D, but they have a difference:
- Those in column B are aligned to the left, this is a serious ERROR, they must automatically align to the right. Those in column D are aligned to the right, this is a
RIGHT.
THIS IS SOLVED: using only the number pad key.
Clarifying: on one machine it can be the period (.), But on others it can be the comma (,), it will depend on the installation of Windows.
1.5.2. Numerical values.
Practice # 15 Autofill or Replicate
Excel allows autofill the columns and this is also called Replicate. For example: You need to list cells with numbers from 1 to 4.
Steps:
- We key in the number 1. We stop with the mouse on the autofill marker or fill handle, the mouse takes the form of more black () and with the CTRL key held, we drag it to the cell that will store the last value. (this is called REPLICATE)
STAYING:
Other examples:
Example 1: Two by two (2,4,6…)
- Type 2,4 and mark them (dragging the whitest) We stop with the mouse on the autofill marker or fill handle, the mouse takes the form of more black () and we drag it to the cell that has the last desired value. (REPLY)
Example 2: The name of all the months of the year needs to be shown.
You type:
A name of the month, it is replicated and it looks like this:
BUT: if you want to REPEAT the same name of a month in all the cells, CLICK the name and with the CTRL key held REPLICAS.
it is repeated consecutively in order. (it happens because they are personalized lists, you will be able to personalize YOURS. Practice # 16)
NOTE: The same happens for the names of DAYS OF THE WEEK.
1.6. Save the information.
- Ctrl G or click the button that appears in the Quick Access bar. The following dialog box appears, allowing me to locate the path that will store the file in question:
NOTE: this dialog box changes according to the VERSION of the Windows Operating System BUT the same principle is to locate the destination folder that will store the file and type its name.
- Click on the My Computer icon (1).Change the area of the panel on the right (2), you must double-click on the corresponding disk drive and in each of the folders until you reach the last one. file in question (3) Click on the Save (4) or ENTER button, to finish.
1.7. Write the mathematical operations.
So far we have studied how to enter data of type number and text in a spreadsheet
(HC), to perform the calculations of these numerical data, the first thing to do is tell
Excel, using the = sign, that a mathematical operation will be performed in that cell, either simple or using a complex function that it has. EYE: If the user forgets the = sign, Excel will never know that a calculation will be made.
A FORMULA, is an equation that analyzes the data in the HC and returns a result. They perform operations with the values of the cells, such as addition, subtraction, multiplication, division, comparison, average, maximum and minimum value, count…..
Excel follows a specific order that includes an equal sign (=) followed by the elements to calculate the operands and operators of the calculation.
By default, Excel calculates a formula from left to right, starting with the equals sign. We can control the order in which the calculation will be executed by changing the syntax of the formula. For example: the formula in the figure above gives a result of 11 because Excel calculates the multiplication before the sum. The formula multiplies 2 by 3, resulting in 6, and then adds 5. Conversely, you must use parentheses to change the order: = (5 + 2) * 3, so Excel first adds 5 and 2 and Then multiply the result by 3, resulting in 21.
To execute basic mathematical operations such as addition, subtraction, division or multiplication; combine numbers and generate numerical results, use the following operators:
Order in which Excel executes operations on formulas.
Arithmetic operator Meaning Example
* (asterisk) Multiplication = B5 * C5
/ (slash) Division = B5 / C5
+ (plus sign) Sum = B5 + C5
- (minus sign) Subtraction, Negation = B5-C5
% (percent sign) Percentage Ribbon Number
^ (circumflex accent) Exponent = 2 ^ B5
The exponent sign (^) is normally activated with Shift + the number 6 key (alphanumeric block), but if the keyboard is unconfigured we find it in Sharp Left Alt + 94 (from the numeric block).
Before continuing, we must know the operators that specify the type of calculation to be carried out with the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.
Comparison operators: Two values can be compared with the following operators. When comparing two values with these operators, the result is a logical value, either TRUE or FALSE.
Comparison operator Meaning Example
= (equal) Equal to A1 = B1
> (greater than) Greater than A1> B1
> = (greater than or equal to) Greater than or equal to A1> = B1
<> (different) Other than A1 <> B1
To type these signs:
Left Sharp Alt + 60 returns <(less than)
Left Sharp Alt + 61 returns = (equal sign)
Left Sharp Alt + 62 returns <(greater than)
Practice # 17 Simple calculation operations.
Formulas are equations that perform calculations on the values in the spreadsheet. A formula begins with an equal sign (=).
For example:
- sign = in the cell where you want the result to appear. Click the first operand. You touch the operator involved on the keyboard. Click the second operator involved in the operation. ENTER.
You give Enter and the result is displayed. Returning to the formula cell keeps the result but the operation appears in the Formula Bar.
For example:
How is it done
- You put the sign = in I3
- Click on the content of cell G53
- You play on the keyboard number block the * which means multiplication. - Click on the content of cell H3
- Das ENTER
- The result appears in cell I3 and you click on cell I3 again and you will see the formula in the corresponding bar.
- Now you must replicate for the rest of the data. (See Practice # 15: Autofill or Replicate) - When you reply you will see that the formulas automatically appear:
o = G4 * H4 o = G5 * H5 o = G6 * H6 o = G7 * H7 so on until all the data in the table is completed, this is something important for the application, the automatic calculation of the content of your cells. Since changing a value in one of the cells involved in the operation, it is recalculated without the need for user action.
The Subtraction, Multiplication, Division and Addition Operations (two digits only) are carried out in the same way, in addition you can have the operators distant from each other. For example: I can have numbers in column C and in Column E and the result in column H.
We will see how SUM is performed for more than two operands.
- Standing in the cell where the result should be displayed, we click on the button:
AUTOSUMA that appears in the upper right part of the Home Tab.
For example: you have values in B4 through B9, so you stop in B10 and click on the AUTOSUMA button and = SUM (B4: B9) appears, give Enter and the result will appear.
Remaining:
With this operation what we are adding is a RANGE of cells, remember that from B4: B9 (they are cells B4, B5, B6, B7, B8 and B9) But, the range to add will not always be at the top, this it means that we can have the values in one column and we want the result in another cell of another column, how is it done?
For example:
- Type from B4 to B12 values.Click on the cell that you want the D14 result to appear on the AUTOSUMA.Excel button will not know where are the values that are involved in the SUM,
therefore the user must click on the first value and drag to the last, the range always appears in a box with dashed lines.
- We give Enter to finish.
As you can see the values are in the cells from B4 to B12) and the result appears in D14.
THIS IS IMPORTANT BECAUSE THE USER IS THE ONE WHO CHOOSES THE RANGE THAT WILL INTERVENE IN THE FORMULA.
Excel can also be a simple calculator.
1.8. Tabs with your commands.
Tabs: are tabs that contain all the Excel tools grouped by ribbon, for example, the "Home" tab contains "Clipboard", "Font", "Alignment", etc… Each one contains a series of buttons that They will allow you to carry out specific tasks.
Ribbon: the Band or Ribbon contains the grouped program commands.
Something IMPORTANT whenever it accompanies each Ribbon, at the bottom right, this icon () means that clicking on it will open the characteristic dialog box of the 2003 version of Excel.
1.8.1. Home tab:
1.8.1.1. Clipboard Ribbon:
Paste: (Ctrl V) to reproduce what was cut or copied.
The Transpose option allows me to change the orientation of the columns.
- Mark the data: Select the information to transpose. Click on the new sheet where the information will appear and click on the cell where the information will be pasted from. Click on the arrowhead of the Paste button and when the area, click on the Transpose option.
Cut: (Ctrl X) to move the selection.
Copy: (Ctrl C) to duplicate the selection.
Copy format: click on the cell where the desired format appears and click the button, the mouse takes the form of a brush next to the whitest, then you click with that brush over the text to modify the format. If you click on it, it will be copied once, BUT if you double click on the button, it will copy as many times as you want and to deactivate the button you must click the button again. For example:
To MOVE the selected item.
- Select the item to MOVE. Click the CUT command. Click on the new position and click the PASTE command.
To COPY or DUPLICATE the selected item.
- Select the item to COPY OR DUPLICATE. Click on the COPY command. Click on the new position and click on the PASTE command.
NOTE: You can repeat step 3 (in both examples) as many times as you need to repeat the element.
1.8.1.2. Ribbon Source:
Font: allows you to choose a different font (you must select and click on the arrowhead and click on the new type) Font size: allows you to choose a size for the font (you must select and click on the arrowhead and click on the desired number, but if you need a value that is NOT in the list, type it and give ENTER)
Increase the font size: clicking on the command increases the selected element in size (the more you click, the more it increases)
Decrease the font size: when clicking on the command, the selected element decreases in size (the more you click, the more it decreases)
Bold: (Ctrl N) allows you to darken the selected text.
Italic: (Ctrl K) allows you to skew the selected text.
Underline: (Ctrl S) allows you to underline the selected text.
Borders: Applies borders to the selected cells.
Click on the arrowhead that accompanies the button, the area as shown in the image opens, allowing me to choose the type of border to use
Click the button directly when you need to repeat the type of border that appears active.
Draw border: Clicking on this option the mouse takes the shape of a pencil and the user can draw the EXTERNAL border only. Draw Border Grid: Clicking on this option takes the mouse to pencil shape with a grid and the user can draw ALL edges.
Delete border: when clicking on this option, the mouse takes the shape of an eraser and the user can delete the border that this shape passes over. Line color: clicking on this option opens a new area that shows the different colors, click on the desired one. The More Colors button allows you to choose other shades.
Line style: when you put the mouse on this option, a new area opens (as shown in the image), you click on the border style to choose.
More Borders….: Activate the Format Cells dialog box with the Borders Tab active:
- It allows to choose the Style of the line. It allows to choose the Color of the lines. It allows to choose one of the three options. It allows to choose among the seven options all that you want.
Fill Color: Colors the background of the selected cells. The No Fill button turns off the color. The More Colors button allows you to choose other shades.
Font color: clicking on the arrowhead opens the range of colors, click on the one you want. The More Colors button allows you to choose other shades.
Clicking the arrowhead () that accompanies the title displays the Font tab in the Format Cells dialog box. Allows you to choose the same commands that are presented in the Font ribbon.
1.8.1.3. Alignment Ribbon:
Align at the top: Aligns the text at the top of the cell.
Align in Middle - Aligns the text so that it is centered between the top and bottom of the cell.
Align at Bottom: Aligns the text at the bottom of the cell.
Align the text to the left.
Center.
Align the text to the right.
Orientation: Rotates the text to a diagonal angle or a vertical orientation. It is generally used to label narrow columns. For example:
You click on the fourth
(Rotate text up) option Wrap text: Makes all content visible within a cell by displaying it on multiple lines.
For example:
Typing the text will take (apparently) two cells (F and G) but clicking on the command adjusts it.
Merge and Center: Joins the selected cells into a larger cell and centers the content in the new cell. Generally used to create labels that span multiple columns.
For example:
The cells involved are selected and the button is clicked.
Clicking the arrowhead () that accompanies the title displays the Alignment tab in the Format Cells dialog box.
1.8.1.4. Ribbon Number:
By clicking on the arrowhead that accompanies the button, an area like the one shown in the image is displayed, here we can choose the style that is desired for each of the options.
COMMENT: One hundred more comfortable working all these styles through the Format Cells dialog box.
the Number tab, select the cells and click on the desired Category and in the right panel make the changes and OK.
Accounting number format - Select an alternative currency format for the cell. In the More accounting formats option, show the Number tab in the Format cells dialog box, here you will find more currency symbols.
Percent Style: Shows the value of the cell as a percentage.
For example: 50 - 100%
20 - x X = 20/50
= 0.4 the cell that contains that value is formatted as a percentage style and returns 40%, notice it is not multiplied by 100 because if that happens when the percentage style is applied it returns 4000% and that is NOT CORRECT.
Thousands style: shows the value of the cell with thousands separator. For example: 400,000.00 (the period (.) Or the comma (,) are used depending on the Windows configuration of the PC you are working on)
Increase decimals.
Decrease decimals.
Clicking the arrowhead () that accompanies the title displays the Number tab in the Format Cells dialog box.
1.8.1.5. Ribbon Styles:
Allows you to highlight interesting cells, emphasize unusual values, and display data using data bars, color scales, and criteria-based icon sets. Clicking on the arrowhead accompanying the button displays an area like the one shown in the image.
We will only study the first RULE.
Clicking on the first option displays the area in which we can choose one of the options shown.
For example: greater than 150.
- The range of cells is selected.Click on the Conditional Format command, in the area that opens, go to the first rule.
Highlight cell rules.
- And click on the option you want, in this example It is greater than… a dialog box opens which allows me to type the criteria and choose the rule. In the first blank area we type 150 and on the arrowhead of the drop-down list of styles is clicked to choose the desired one. Accept to finish.
NOTE: Clicking the Custom Format option opens the Cell Format dialog and allows you to customize your conditional format.
OTHER FORMATS WILL BE STUDIED INDIVIDUALLY.
Clicking on the arrowhead accompanying the button opens an area displaying different styles, allowing you to quickly apply a format to a set of cells and convert it to a table by selecting a predefined Table Style.
Clicking on the arrowhead accompanying the button opens an area that displays different styles, allowing you to quickly apply a format to a cell with the predefined styles.
You can also define your own styles for cells.
1.8.1.6. Cells Ribbon:
It allows to insert cells, rows, columns and sheet.
Selected all the column (s) or the row (s) with
Ctrl + (you can insert)
It allows to delete cells, rows, columns and sheet.
Selected all the column (s) or the row (s) with
Ctrl - (you can delete)
These commands were taught in other ways at the beginning of the material in Practice # 2: Modify Row Height…
Practice # 5, # 6, # 14 Practice # 1: Modify Column Width…
Practice # 11: Hide or Show.
Practice # 7: Rename or Rename.
Practice # 8: Move or copy… Practice # 10: Label color.
Practice # 9 and 9-1: Protect sheet…
Practices are shorter paths.
1.8.1.7. Modify Ribbon:
Autosuma: it allows us to perform the SUM more quickly but from a group of cells.
For example:
- We select a group of cells and and the sum appears below the selected range. Activate the cell that will store the SUM, we click the button and = SUM appears () then we must select the range of our data to SUM.
With this button we also have access to other functions using the arrowhead of the button. Clicking on it will bring up the image drop-down list. And we can use another function that is not the Sum, such as Average (calculates the arithmetic mean), Max (gets the maximum value) or Min (gets the minimum value). In addition to being able to access the functions dialog through More Functions…. (We will study them when we get to the FUNCTIONS with the highest degree of complexity)
IMPORTANT: To use these options, make sure that you have selected the cell in which you want the operation to be carried out before pressing the button.
Organize the data to facilitate analysis.
You can sort selected data from least to greatest (ascending) or greatest to least (descending) or temporarily filter specific values.
Sort from A to Z: ascending.
Sort from Z to A: descending.
Custom order: we select the range of values to SORT, we click on the option and the dialog box is activated which allows me to select on the button order by: the column by which the order is to be performed.
In the Sort by option, clicking on the arrowhead selects one.
In the Sort criteria option one is selected.
Accept to finish.
Filter: enable the filter in the table heading header, once activated click on the arrowhead to choose an option to filter.
(the filter allows to simplify the data list)
1.8.2. INSERT tab
1.8.2.1. Graphics Ribbon:
A graph is the representation of the data in a spreadsheet for easy interpretation. The use of graphs makes the interpretation of the data easier and more immediate. Often a graph tells us much more than a series of data classified by row and column. When creating a chart in Excel, we can choose to create it:
- As an embedded graphic: Insert the graphic on a normal sheet like any other object. As a graphic sheet: Create the graphic on a sheet exclusively for the graphic, on graphic sheets there are no cells or any other type of object.
It is recommended that you have selected the range of cells that you want to participate in the chart, this way, Excel can automatically generate it.
Insert a column chart: Column charts are used to compare category values.
Clicking on the arrowhead that accompanies the button shows a group of styles for this type of chart, click on the one you want and activates the SHEET Chart Tool with three ribbons:
Line; Circular; Bar; Area; Dispersion; other charts: the options are the same for the different types.
Ribbon Design:
Change Chart Type Changes to a different type of chart.
Save as template Save the format and design of this graphic as a template that you can apply to future graphics.
Switch between rows and columns Exchanges the axis data. The data that has been placed on the X axis will be moved to the Y axis and vice versa.
Select Data Changes the range of data included in the graph.
Chart layouts Change the overall layout of the chart. (click and allow to choose between 10 types of graph)
Design Styles Changes the overall visual style of the graphic. (click on and choose between 48 styles)
Move Chart: Moves this chart (only) to a new sheet in the workbook.
Ribbon Presentation:
In the Presentation tab you can find all the options related to the appearance of the graph. They are self explanatory.
For example, on the Axes ribbon, you can decide which ones to display or whether you want to include Grid Lines to better read the results. Ribbon Format:
They are self explanatory.
Clicking the arrowhead () that accompanies the title displays the Change Chart Type dialog box.
1.8.3. PAGE DESIGN tab
1.8.3.1. Ribbon Page setup:
Before printing a spreadsheet, it is advisable to configure the page, to modify factors that affect the presentation of printed pages, such as orientation, headers and footers, paper size,…
In the ribbon Configure page we will find quick buttons to many of the options: to configure Margins, Orientation, Size, etc. But if what we want is to access all the configuration options, we must press the arrowhead in the lower right corner. A new window will open, the Page Setup dialog box and it is organized in several tabs.
Margins: Select the margin sizes for the entire document or for the current selection.
Click on the last option custom margins… and the Page Setup dialog box is activated BUT with the Margins tab active.
Page Orientation - Changes the orientation of the sheet.
Page size: Choose a paper size for the current section.
Click on the last option More paper sizes… and the Page Setup dialog box will be activated BUT with the Page active tab.
Mark a specific area of the sheet for printing.
Activate the Page Setup dialog box with the Cells tab active.
Lets you repeat the header of the data on all the pages of the sheet. This command DOES NOT ALLOW SELECTED SHEET GROUPS and can NOT be performed by the PRELIMINARY VIEW command.
Repeats the table heading for all the pages on the sheet.
When the table content exceeds the page, this function allows repeating the selected heading on all the pages of the sheet, the heading can contain: text, images, lines. Steps:
- Click on the command Print titles or click on the arrowhead that accompanies the title to activate the dialog box that opens and click on the Sheet tab and there click on the red arrow that accompanies the option Repeat rows in the upper end, the area is closed, you click on the row header to repeat, you click the red arrowhead again showing the range of cells to repeat. For example: $ 1: $ 5, it will repeat from row 1 to 5 only and everything inside that RANGE. Click on the Accept button to finish.
You can also Repeat columns on the left. BUT you must be careful if you have a logo and it takes part of a column that you do not repeat.
NOTE: To see what happens you must click on the Preview button.
Click on this ARROW POINT that appears in the lower right () and activates the following Page Setup dialog….
The PAGE SHEET allows in the option:
- Orientation: choose between Vertical or
Horizontal.
- Scale Adjustment: adjust to a PERCENT of the normal size (less percent will be the impression of the model and vice versa). Paper size: choose Letter or Lether which is the normal sheet 8 x 11.Click on the OK BUTTON if finished.
The MARGINS SHEET allows:
- It allows to modify the margins, these vary according to the need of the model in question. If you want to save a sheet, the left and right margin goes to 0 (zero); the top and bottom should NEVER be smaller than that of the Header and the Footer BUT the Header and / or Footer is used, the Lower may be changed to 0.5. In the Center on page option use Horizontally and / or Vertically as required. (I always use
Horizontally only).
- Click on the OK BUTTON if you have finished.
The HEADER AND FOOT PAGE SHEET allows:
The Customize Header BUTTONS… (at the top of the printed sheet) and Customize Footer… (at the bottom of the printed sheet).
Let's Customize Header…
- Click on the button that identifies it, activating a new dialog box:
Which is divided into three sections, by clicking on them they are activated as appropriate. In the sections you can insert lines, page #, page #s, date, time and text (format it), using the following buttons:
Apply formatting to text.
Insert page number. Clicking on the button displays &.
Insert number of pages. Clicking on the button displays &.
These two commands are used together for example:
Sheet & of & -– remaining in print Sheet 1 of 5.
& / & -–] -– remaining in print 1/5
MEANS: The first number is the current page and the second number is the number of pages that SHEET has.
Insert the date. Clicking on the button displays &. BUT the DATE of the moment you print the file.
Insert the time. Clicking on the button displays &
A small space is left between the date and time if used in the same section.
The four COMMANDS that are activated is a language for the machine, she chooses the date and time of the PC and knows how to find the consecutive page and the number of pages that the LEAF in question has, BUT THE COMMANDS CONFIGURATION IS NEVER CHANGED: &;
&; & and &.
- OK to close the second dialog box on completion. OK to close the first dialog box on completion.
NOTE: these changes are only seen if you CLICK on the PRELIMINARY VIEW () command that appears in the Quick Access BAR, it can be changed in position according to the user's convenience, the arrowhead that accompanies the Bar allows me to customize Commands (click on it and in the area that opens, I click on the command to customize (one by one).
NOTE: These three tabs can be used with SELECTED SHEET GROUPS.
THE SHEET SHEET:
NOTE: this SHEET is used only for individual sheets NEVER selected sheet group. This function is used when the sheet has more than ONE page.
- By clicking on the area that accompanies the Repeat rows option in the upper end and clicking on the table area where the rows that will be repeated are located, in that area it will appear, for example: $ 1: $ 5 that means they are absolute rows that everything found in them will be repeated (can contain: text, images, lines and above all the table heading.
ALL OF THIS INFORMATION IS REPEATED ON EACH PAGE OF THAT SHEET.
You can use the Repeat left columns option: this option is used for a LEAF
It has many columns with information so many are filled to the right and columns are repeated for ALL the pages of the SHEET.
1.8.3.2. Preview button.
Click the (Preview) button. A new presentation view of the book appears and with it the bar like the one shown in the figure.
Clicking on the Page Setup button brings up the same dialog box as previously discussed.
1.8.4. DATA tab
1.8.4.1. DATA Ribbon.
Lets you narrow the list of data based on criteria (conditions that are specified to limit the records that are included in the result set). You can also filter on more than one column. The additional filters are based on the current filter and also reduce the subset of data.
Activate the filter (for greater security, activate a cell in the table header or select all of them). Once the filter is activated
An area like this is activated:
You can see how the button we used for the filter changed and now it is this, to return to the full list click on this button and select the option (Select all) and
You can use filters inside each other. For example: of the F, how many are 60 years old and how many are of race B, so you simplify the list even more.
1.8.4.1. Data Tools Ribbon.
In spreadsheets, users will write data to get the results and calculations they want. Securing valid data entry is an important task.
You may want to restrict the data entry to a certain date range, limit the options using a list, or ensure that only positive integers are entered. It is also essential to provide immediate help to instruct users and clear messages when invalid data is typed to make the data entry process go smoothly.
You can also force the data to be chosen from the values specified in a drop-down list.
Once you decide which validation you want to use in a spreadsheet, you can configure it by doing the following:
Steps:
- Mark the cells involved. Click on the Data tab On the Data Tools ribbon, click on the Data Validation command and a Data Validation dialog box will open, such as the one shown and on the Configuration tab:
we click on the arrowhead of the Allow option:
and we choose the List option, changing the presentation of the dialog box:
Here we can choose the Origin from the list:
We click on the Origin option area and select the list (it must be outside the printing range of the table in question). For example: $ AF $ 3: AF $ 5 (this is the range shown from my hypothetical list) that means that that cell group is ABSOLUTE.
- Click on the OK button to finish. But we must HIDE the column that has the list: Mark the entire column (from the header). Right-click on the marked area and in the contextual menu that appears click on the HIDE option. SHOW IT: Mark the previous and the subsequent one (from the header) Right-click on the marked area and in the contextual menu that appears, click on the SHOW option.
ANOTHER WAY to use validation is to give the LIST a NAME.
STEPS:
- We type the list on another sheet. Right-click and the context menu that opens click on the Assign name to a RANGE option. A dialog box opens, the name of the range is typed and we give OK.
NOTE: I put the name of the RANK as the heading of the LIST.
- Then we hide the sheet that saves the range or ranges that I have named. (Practice # 11: Hide and Show) THEN in the Origin area we type = (and the name that we gave to the RANGE of the list in question).
An arrowhead appears in each cell involved, when clicking on it, an area is displayed showing the content that the user had in the list. You click on the desired option, so on in each cell.
In the Origin area we can also type the list, separated by pt. and comma (;) BUT if when clicking on the arrowhead and the data are next to each other the separator we are using is not the correct one, you must change them with the comma (,) because the list must be one below the other.
On the Input Message and Error Message Tabs, type as appropriate or simply NOT use.
1.8.5. VISTA tab
1.8.5.1. Book Views Ribbon.
The following buttons also have the same function at the bottom right as those on the Book Views ribbon:
Normal view
Page layout view
Page break preview
Zoom minus sign (smallest display area) and vice versa.
Normal - displays the sheet in normal view.
Page Layout view - displays the book as-is
will appear on the printed page. You can use this view to see where pages start and end ONLY (you can see other things, I don't use it for anything else). To return, click on the NORMAL View button.
displays a preview for the user to see where the first, second, third page ends, and so on.
The user will be able to move the blue lines to change the sheet settings, to change the printing area.
displays the sheet on the screen only.
1.8.5.2. Ribbon Window.
Opens a new window containing a view of the current book, identifies it by name
of the file.xls: 2, this number changes according to the views you make.
Clicking on the button opens an Arrange Windows dialog box (shown on the left) which allows me to choose different options. For example, Horizontal (places the work areas of the book one below the other) to deactivate it by clicking the maximize button on one of them. Vertical (places the work areas of the book one on the other) to deactivate it by clicking the maximize button on one of them. Click the Accept or Enter button to finish.
Note: Those are the ones I use the most.
It is used for when the table is very long (it has a lot of information on the right and at the bottom.
Keeps a portion of the selected sheet visible while scrolling through the sheet.
I always use the first option (Freeze honeycombs).
Steps:
- Click on the cell from which I need to freeze. (D4) A line appears dividing the area vertically and horizontally.
When moving within the table, the left and upper part of the selected cell are FIXED.
In the same position of Freeze Panels the opposite option Mobilize Panels appears.
Divide the workspace into four adjustable panels containing the views of your book. You can use this function to view several different parts of a book at once. This button toggles the option on and off.
I can move these lines by standing with the mouse on them (vertically or horizontally), taking the mouse in these shapes and dragging.
Hide the current window.
Show hidden window. If there are more than one, they are selected one by one.
Change from one window to another. Depending on the number of open books, click on this button and click on the one you want to show (of those that are only open).
REMEMBER: they are reset by clicking the MAXIMIZE button that is displayed in a single window.
1.9. Customize lists.
To personalize a list we must be clear that the values of the list will not change in a longoooooooooo period of time (for example: the provinces of Cuba, the names of the hotels according to the hotel chain, the hotel chains,….)
- Click on the Office button and in the area that opens () A new dialog box (like the one shown) is activated, it allows me to:
- In the Inputs area, the lists are typed (one and Enter; the second and Enter….) and the Add button is clicked when finished.
Another way:
Import lists from cells: this option allows me to locate and select a list that has been previously typed, we click on the Import button (for example: the list range $ C $ 5: $ C $ 20 will appear in the area)
- Click on the OK button to finish.
NOTE: if you made a mistake, click on the Delete button.
- Click the OK button in the first dialog box that was displayed. (Two boxes two Accept).
1.10. Some FUNCTIONS.
Functions are predefined formulas that execute calculations using specific values, called arguments, in a certain order or structure.
The functions can be used to execute simple or complex operations. = FUNCTION_NAME (arguments)
FUNCTION_NAME: It is the name of the Excel function, such as: ROOT, SUM, SUM, YES, AVERAGE, SIN, COSINE, MAX, MIN, COUNT, AND, OR, COUNTA.
Arguments: They are the values that a function uses to execute the operations or calculations. The type of argument that a function uses is specific to that function. The most commonly used arguments are: numeric values, text values, cell references, cell ranges, names and functions.
Paste Function
- Click on the cell where the FUNCTION result should appear. Click on the Paste function button. The following dialog box appears, where the categories and names of each function appear. You selected the function to use by clicking on it and Click on the OK button, the box that allows me to type the arguments appears. Click on the OK button to finish.
AVERAGE
Returns the average (arithmetic mean) of the arguments.
Syntax
AVERAGE (number1; number2;…) where:
Number1, number2,… are between 1 and 30 numeric arguments whose average you want to obtain.
SUMMARIZING: AVERAGE (range)
Example:
- In H16 and I16 we combine and type a text (Group Average) and we click on J16 to find the average. Click on the fx button. In the Insert Function dialog box, we choose the AVERAGE function and click on OK The box where the arguments are filled appears: We select the range J4 to J13 (we see how the box with dashed lines appears) Click on the OK button.
STAYING: In J16 when giving Enter the result will appear.
MAX
Returns the maximum value of a set of values.
Syntax
MAX (number1; number2;…)
where:
Number 1; number2; … Are between 1 and 30 numbers for which you want to find the maximum value.
SUMMARIZING: = MAX (range)
Examples
If A1: A5 contains the numbers 10, 7, 9, 27 and 2, then:
= MAX (A1: A5) equals 27
If A1: A5 contains the numbers 9, 10,37,34,11 and C1: C5 contains 68,58,67,42,45, then:
= MAX (A1: A5, C1: C5) equals 68
MIN
Returns the minimum value of a set of values.
Syntax
MIN (number1; number2;…)
where:
Number1; number2; … Are between 1 to 30 numbers whose minimum values you want to find.
SUMMARIZING: = MIN (range)
Examples:
If A1: A5 contains the numbers 10, 7, 9, 27, and 2: MIN (A1: A5) equals 2
IMPORTANT:
The AVERAGE, MAX and MIN functions have the same argument: range, which makes it very easy to understand, we can also search for it by clicking on the arrowhead of the AUTOSUMA button:
, the area that displays them is displayed and you click on the function and follow the steps
that you have already studied.
YES
Checks if a condition is met and returns one value if it evaluates to TRUE and another value if it evaluates to FALSE.
Syntax:
YES (logical_test, value_if_true, value_if_false)
where:
logical_test: is any value or expression that can be evaluated as
True or false.
value_if_true: is the value that will be returned if the logical_test is TRUE.
false_value: is the value that will be returned if logical_test is FALSE.
Example 1:
Suppose a spreadsheet contains current and forecast spending figures. Cells B2: B4 contain the "Current expenses" for January, February and March: $ 1,500; $ 500; $ 500. Cells C2: C4 contain the "Forecast Expenses" for the same periods: $ 900; $ 900; $ 925. Remaining:
With the following formulas you can write a formula that checks if the budget has been exceeded:
IF (B2> C2, »Budget exceeded», »Accept») is equal to «Budget exceeded» IF (B3> C3, »Budget exceeded», »Accept») is equal to «Accept»
Staying with the FORMULA:
How is it done?
Steps:
- Click on the cell that will store the function. Click on the fx button. The Insert function dialog box opens and click on the function to use (in our case click on the YES function) and click on the OK button. REMEMBER: if it does not appear in Recently used, look for the option ALL.
We click on the first argument:
- Logical_test, we click the logical_test argument, and type the criteria, Examples: B2> C2; B2 = "apple"; B2> = 500. (Excel defaults to putting text data in quotation marks. In the argument Value_if_true: we type Budget Exceeded. In the argument Value_if_false: click OK. Click the OK button to finish. We replicate the formula to fill the rest of the students. IF MET THAT THE AVERAGE IS GREATER THAN 90 THE STUDENT WILL BE EXCELLENT, BUT IT WILL BE WELL.
This formula allows you to know if the Budget is going as planned, these NOTICES are widely used because it warns you of what may happen, and you can also use Conditional Formatting to change the color of what is PLANNED.
NESTING FUNCTIONS solve many problems, but for people who do not have much practice it is VERY DIFFICULT. Here I gave you two examples.
An example of the nested IF function with the SUM function. Example 1: IF (A10 = 100, SUM (B5: B15), »»)
Activate C17 and type the formula.
In the following example, if the value in cell A: 10 is 100 (you type the value you want, BUT the condition is only met if the cell has a value of 100), logical_test will be TRUE and the total value of range B5 will be calculated: B15. Otherwise, logical_test will be FALSE and an empty text string ("") will be returned that will delete the contents of the cell containing the IF function.
How to do it? Steps:
- Click cell C15. Click the fx button. The Insert Function dialog box opens and click on the function to use (in our case click on the YES function) and click on the OK button. REMEMBER: if it does not appear in Recently used, look for the option ALL.
We click on the first argument:
- Logical_test, we click the logical_test argument, and type the criteria: A10 = 100.
In the argument Valor_si_false: click and type the quotes. It MUST be double quotes (ASCII of the quotation marks, sharp left Alt and number 34), ALWAYS TWO with no space between them, they CANNOT be simple.
- In the argument Value_if_true: click and choose the SUM function
A new Function Arguments box opens but now for the SUM, we choose the range:
Accept to finish.
An example of the IF function nested multiple times.
Example 2:
Suppose you want to qualify reference numbers with letters Average. Greater than 89-A; From 80 to 89-B; From 70 to 79-C; From 60 to 69-D; Less than 60-F
The following nested IF function could be used:
= YES (B2> 89; »A»; SI (B2> 79; »B»; SI (B2> 69; »C»; SI (B2> 59; »D»; »F))))
How to do it:
- Click on the fx button. Choose the function and activate the argument dialog box: type 1st logical_test: In the argument area Value_if_fake we click on the arrowhead of the cell name area and choose the Yes function again. of arguments dialog: we type 2nd logical_test: In the argument area Value_if_false we click on the arrowhead of the cell name area and choose the function Yes again An argument dialog box opens again: we type 3rd logical_test: In the area of the argument Value_if_false we click on the arrowhead of the cell name area and choose the function Si again. An argument dialog box opens again: we type the 4th and last logical_test and fill in the remaining arguments: Accept to finish.
COUNT YES
Count the cells in the range that match the given condition.
Syntax
COUNT YES (range; criteria)
where:
range: is the range from which you want to count the number of cells that are not blank.
criterion: is the condition in the form of a number, expression or text that determines which cells must be counted.
Example:
I have data in a table and I want to know how many are FEMALE.
How is it done?
Steps:
- Click on the cell that will store the function. Click on the fx button. The Insert function dialog box opens and click on the function to use (in our case click on the COUNT.SI function) and click on the button To accept. REMEMBER: if it does not appear in Recently used, look for the option ALL. A new dialog box opens where you type the arguments: Click on the OK or Enter button.
Returns the number of Females: for example 5.
COUNT IF SET
Counts the number of cells that meet a certain set of conditions or criteria. (you have to use 127 criteria)
Syntax
COUNT.SY.CONJUNTO (rank1; criterion1; rank2; criterion2…)
where: rank1, rank2,… are from 1 to 127 ranges in which the associated criteria will be evaluated. The cells in each range must be either numbers, or names, arrays, or references that contain numbers. Blank and text values are ignored. criteria1, criteria2,… are from 1 to 127 criteria in the form of number, expression, cell reference or text that determine the cells to be counted. For example, the criteria can be expressed as 32, "32", "> 32", "apples" or B4.
Example:
I have data in a table and I want to know how many are FEMININE, MESTIZOS and CÁRDENAS.
How is it done?
Steps:
- Click on the cell that will store the function. Click on the fx button. The Insert function dialog box opens and click on the function to use (in our case click on the COUNT.SI function) and click on the button To accept. REMEMBER: if it does not appear in Recently used, look for the option ALL. A new dialog box opens where you type the arguments:
- I choose the range, I type the criteria, I choose the range, I type the criteria, I choose the range, I type the criteria.
Notice at the right end a scroll bar opens.
- Accept to finish.
STAYING:
CONCATENATE
Join multiple text elements into one.
Syntax
CONCATENATE (text1; text2;…)
where: text1, text2,…: are between 1 to 255 text elements that will be united in a single element and that can be text, strings, numbers, or simple cell references.
Example:
Data in the table:
How is it done?
Steps:
- Click on the cell that will store the function (F2) Click on the fx button The Insert function dialog box opens and click on the function to use (in our case click on the CONCATENATE function) and click on the button To accept. REMEMBER: if it does not appear in Recently used, look for the option ALL. A new dialog box opens where you type the arguments:
- Click on the first argument and click on the first cell Click on the second argument and type quotation mark give a space and type quotation mark again BUT those quotation marks are flat (left Alt held and number 34 of the number block), they they do the function of the space. GO TO the last cell.
- Accept to finish.
VLOOKUP
Searches for a value in the first left column of a table, and then returns a value in the same row from a specified column. By default, the table is sorted in ascending order.
Syntax
VLOOKUP (searched_value, search_array, columns_indicator, ordered)
where:
searched_value: is the searched value in the first column of the table. search_array: can be a reference to a range (all data in the table) or a range name. column_indicator: is the number from which the matching value should be returned. (each column receives a different value).
ordered: is a logical value (FALSE - so you assume the list does not have to be ordered)
Example:
Data in the table: Enter the name and surname of a person from the database and return all the DATA. Sheet1:
Sheet2:
How is it done?
Steps:
- Click on the cell that will store the function (B4) Click on the fx button. The Insert function dialog box opens and click on the function to use (in our
case click the VLOOKUP function) and click the OK button. REMEMBER: if it does not appear in Recently used, look for the option ALL.
- A new dialog box opens where you type the arguments:
Lookup_value: is the cell where you type the name of the person you want to find. search_array: the DATA range of the entire table. column_indicator: is the column number (in this case the first and last name is 1, so on. sorted: FALSE, never having to sort the table.
IMPORTANT: you must put B2 and B5: O54 ABSOLUTE, I remember to press F4 inside the cell names area, inside the argument box or in the Formula bar area.
LUCK! Well, I think you should already be able to do many more things alone.
Download the original file
