Get familiar with Microsoft Excel

In parallel with the post about Microsoft Word being posted, we will present a series on Microsoft Excel spreadsheets. I hope that this knowledge will be really useful for you who are new to Microsoft Office suite as well as for office workers.

It is possible that some people who are familiar with Excel spreadsheets do not know that the spreadsheet you are using has a maximum of 256 columns and 65,536 lines; The maximum number of worksheets you can add is 255 sheets. With this huge number, managing tables will become much simpler because it is possible to save 255 tables on 255 sheets in an Excel file.

1, Data types in spreadsheets

When working with spreadsheets you will have to be familiar with a lot of data types, but all those types of data are based on 3 basic types: Number type, Font type and Formula type.

a, Typeface :

Font data is always on the left side of the cell (cell), which includes letters, numbers, and special characters. If a cell has numeric data that you want to convert to text, you must have an apostrophe (') in front of that cell.

b, Number type :

The numeric data is always on the right of the cell. The values ​​of date, time, currency, percent . are numeric data (can be calculated plus, minus, multiply, and divide).

Note : the date value if you enter it correctly (usually month / day / year) will be on the right of the cell, if it is entered incorrectly, it will be on the left of the cell (equivalent to the type)

c, Formula type :

Formula data is data that begins with the signs: =, +, -, * (most commonly, using the = sign).

- Calculation formulas in data types :

Add + Subtract - Multiply * Divide / Percent%

The separator between decimal places is usually a period (.), While the separator between thousands of numbers is a comma (,). (Example: 1000000 = 1,000,000; also ½ = 0.5)

- Logical functions :

• OR : function or
• AND : function and
• NOT : negative function

- In addition to the formula type, it will often be combined with calculation functions (this section will be introduced in the next lesson)

2, Types of addresses

Each cell has a separate address to distinguish and calculate. The address of the cell is named after the column symbol and the corresponding line number of that cell. (Example: cell C3 is in column C, line 3), the address of the cell you will see on the left side of the Formular bar (the toolbar is just above the worksheet).

There are 4 types of cell addresses you must remember : Use F4 to change between address types

- Relative address : is a normal address that you often see, this address will change the whole column when copying the formula (this section will introduce later). The relative address symbol is: tencottendong ( Example: C3 )

- Absolute address line : is the address whose line does not change but the column changes. The symbol of the absolute address line is: tencot $ tendong ( eg, C $ 3 is the absolute address of line 3 )

- Absolute column address: is the address where the column does not change but the row changes. The symbol of the absolute address column is: $ tencottendong ( example: $ C3 is the absolute address of column C )

- Absolute address : is the address where both the column and the row are not changed when copying the formula. The symbol of this address is: $ tencot $ tendong ( example: $ C $ 3 is the absolute address for both column C and line 3 )

Address space : you will have to use the address space very much when making spreadsheets, this address area shows which box you are choosing from. The address space symbol is as follows: tencot1tendong1: tencot2tendong2 (For example, you are selecting the area from cell C3 to H5, the address space will be C3: H5)

3, Alignment, format data in spreadsheets

If you have seen the article about Microsoft Word, then you should also know some basic knowledge about text alignment. Because we work with spreadsheets primarily as a calculation, the alignment is not much, but it should be enough for other people to see your tables that can understand its structure.

Editing data in cells : there are 3 ways

- Double click the left mouse button to edit

- Select the cell to edit then press F2

- Select the cell to edit and edit the contents of the box on the Formular Bar

If you want to align and format the part, you must first highlight it. The next steps are as follows:

a, Display data format format :

Go to Format -> Cells . -> Number

- Genaral : basic default type when you enter.

- Number : number type that distinguishes decimal part from

• Decimal places : number of digits displayed after the decimal part
• Use 1000 Separator (,) : whether to use the separator (,) between thousands of parts
• Negative numbers : format for negative numbers.

- Currency, Accounting : currency type

• Symbol : currency type of country

- Date : type of date

- Time : time type

- Percentage : percentage

- Fraction : fractions

- Scientific : acronym type

- Text : font style

- Special : special type

- Custom : user-format style

For example, you want to format the display date (the number displayed may differ from the actual number you entered) in the form of date / month / year: the format in Custom is dd / mm / yyyy (d-day, m - month, y-year)

b, Align, format data in cells :

Go to Format -> Cells . -> Alignment

- Text alignment : align the margins for data

• Horizontal : horizontal alignment of cells
  + General: by default, the input data is alphanumeric
  + Left (Indent): base on the left of the cell
  + Center: centered on the middle of the cell (horizontal)
  + Right (Indent): align to the right of the cell
  + Fill: fill the cell with the data already in the cell
  + Justify: flat on both sides
  + Center Across Selection: center by selected area (blacked out area)
• Vertical :
  + Top: align with the top edge of the box
  + Center: the base is in the middle of the box (vertical)
  + Bottom: align with the bottom edge of the box
  + Justify: justify
  + Distributed: self-format.

- Text control :

• Wrap text: allows the data to flow in the cell (if you want to down the data line at your own discretion, place the cursor before the position that needs to be down and press Alt + Enter)
• Shrink to fit: automatically shrinks data when the cell is minimized
• Merge cells: mix cells

- Right-to-left : the direction of writing text

• Contex t: depending on the data entered
• Left-to-right : write from left to right
• Right-to-left : write from right to left

- Orientation : oriented to display text horizontally or lying along the cell, can adjust data diagonally by dragging the text axis or selecting the rotation of the text in the box Degrees below.

c, Font format

Go to Format -> Cells . -> Fonts

This section is similar to Word with:

- Font : font format

- Font style : italic style, bold or normal

- Size : font size format

- Underline : underlined style

- Color : font color

- Effects :

• Strikethrough : format the middle hyphen
• Superscript : index on
• Subscript : index below

d, Format cell borders, table borders :

Go to Format -> Cells . -> Border

- Presets : pre-positioning for borders

• None : no borders
• Outline : external outline format
• Inside : internal border format

- Border : detailed border format with upper, lower, left, right, mid-stroke and horizontal strokes.
- Line : contour type

• Style : contour styles
• Color : color for the border

If you want to format the color for the cell , go to Format -> Cells . -> Patterns and choose the color.

In addition, if you want to format quickly, you can use the functions available on the Formatting toolbar to format

4, Delete the entire format did

In case you have finished formatting but want to delete the format to redo (delete without losing data), do the following:

- Black out all data that you want to remove
- Go to Edit -> Clear -> Format

5, How to calculate and copy formulas in Excel

a, Enter the calculation formula :

If you want to calculate the value of any cell, use that cell's address to calculate

For example: If you want to add 2 numbers at address B5 and C5 address together, the formula in the cell should set the result = C5 + B5 (not necessarily remember the address of the cells, you just need to type the equal sign (=) then click on cell B5, type the plus sign (+) and then click on cell C5, then press Enter to get the result)

b, Copy the formula :

If with a table like the image above, you don't have to calculate the sum for each row of the table, just enter the formula in a single cell in the result column. After entering the formula and press Enter, place your mouse in the lower right corner of the cell (the mouse pointer appears as a black plus sign) and hold the left mouse button up or down (you can drag across the row if results are in horizontal rows)