In this tutorial you will learn how to create a table in Excel. The table created in this guide is the Employee Salary table, complete with addition formula, subtraction and multiplication.
This guide also briefly explains the parts of Excel. How to set column width and row height in Excel. You can also learn how to add color to tables.
Let's look at this short guide, how to create a complete table with simple formulas that are often used.
Microsoft Excel is a very popular application because this application is widely used by government agencies and companies to process data or create reports.
Microsoft Excel is created and distributed by Microsoft Corporation which can run on Microsoft Windows and Mac OS.
List of contents
Introduction to Excel
In an Excel document or Workbook there are several worksheets called Sheet, By default these sheets are now named with Sheet1, Sheet2 and Sheet3.
Each Sheet consists of many columns and rows, The meeting between columns and rows is called a cell. On cell This is where you can input and process data.
Each column in Excel is represented by the letter A, B, C and so on. and on each line is represented by a number 1, 2,3 and so on.
Identity cell determined by the column name and row number that intersect each other. For example column A row 2, column B row 4, and so on. On Excel, To simplify it, just write the column names and row numbers. Example, column A CLASSIFIED 2 written A2, column B CLASSIFIED 3 written B3, and so on.
Tips:
- To refer to a cell written with column names and row numbers, example B3.
- To reference/highlight some cell that traverses multiple columns and rows, written with a start cell and end cell, and separated by a colon. Example: B3:C9
- To input data in cells, click on the cell, then type the data.
- To change some data in a cell, double click on the cell.
- Apart from inputting directly to the cell, Data can also be input via the formula bar.
- To move between cells, Press the button Tab or Enter
- Every time you enter a formula or function in Excel you must start with the sign " = “
Creating Tables in Excel
To create a table in Excel is not that difficult. How to work in Excel tables is almost the same as how to work in Word tables.
1. Create Column Titles.
We start creating a table in Excel by creating column headings. The column titles that we will make are NO, NIK, SALARY, BENEFITS, MEAL ALLOWANCE, AMOUNT, PIECE, and NET SALARY
Click on A1 then type NO, the result is like the following image.
Then continue by inputting your NIK, SALARY, BENEFITS, MEAL ALLOWANCE, AMOUNT, PIECE, and NET SALARY in each subsequent cell. Once finished the results will look like the image below.
There are some letters that are not visible because the column width does not match the length of the letters, for that we need to adjust the column width.
2. Adjust Column Width and Row Height
To change the column width, Move the pointer to one of the column boundaries you want to change so that the pointer changes shape. Click and drag the pointer to the right so that the column width changes as needed.
Changing the size of columns/rows can be done at once, by highlighting the column or row, move the pointer to one of the boundaries, then drag right/left for columns, top/bottom for rows.
3. Inputting Cell Contents .
We will fill the cell with basic data and processed data.
Basic data is data that cannot be generated from formulas, Processed data is data obtained from formulas.
We fill in the basic data in the "NO" column, "NAME", "SALARY", “ALLOWANCE”, and “FOOD MONEY”.
Meanwhile, the data for the "NUMBER" column, "PIECE", and "NET AMOUNT" we will fill in with formulas so that it will produce a value.
Enter the basic data as in the following image.
Next we will format it into a table.
Formatting Tables in Excel
To format data into a table, we need to highlight all the cells that will be used as a table. To highlight cells, Click on the first column title then drag to the last row in the last column
- Highlight the cell then click menu Format as Table,
- Select the desired table model, for example green.
- Click OK
- After becoming a table, a menu will appear Table Tools (table tools). We can use tools This is for other purposes.
- To make it easier to see the number values, in cells with numbers, we put a thousands separator. Highlight the cell containing the number then click Comma Style
Next is to create a formula to calculate the SUM value, DEDUCTIONS and NET SALARY
Creating Formulas in Excel
After formatting the cells as a table, We only need to create a formula in the first row for each column. Excel will automatically apply the formula to each row below it in the table.
1. Formula for AMOUNT.
The value of AMOUNT is the sum of the BASIC SALARY, ALLOWANCES and FOOD MONEY. The formula is:
TOTAL = BASIC SALARY + BENEFITS + MEAL ALLOWANCE
To calculate the amount, we can do with the operator “ + “ (plus sign) or by using functions SUM.
Formula / the formula to find the amount is: SUM(initial_cell : end_cell)
In the amount column, type " =SUM( “ , then highlight from BASIC SALARY to FOOD MONEY, Then Enter. Or you can also type " =SUM(D2:F2) " Then Enter
2. Formula for CUTS.
In this scenario, The company determines a discount of ten percent (10%) of the salary amount, so the formula is:
CUTS = AMOUNT * 10%
In the CUT column, type " = “ then click AMOUNT, then type “ * “(star sign) then type “ 10% “, Then Enter
3. Formula for NET SALARY.
NET SALARY is the AMOUNT minus DEDUCTIONS, so that's the formula:
NET SALARY = TOTAL – CUTS
On NET SALARY, type " = “ then click AMOUNT, then type “ – “, then click CUT, Then Enter
We have succeeded in obtaining the NET SALARY value using the formula.
At this point the process of creating a table in Excel has been completed, You may want to know how to insert columns in Excel.
Inserting Columns in Excel
The next scenario is, The boss asks you to add job title data to the employee salary table. Ideally, we insert the POSITION column before the NET SALARY column.
- Right click on the NET SALARY column then select insert
- Change the column title to POSITION.
- Fill in the Department data.
Finished
Table creation in Excel has been completed. Hopefully this brief guide can help you in creating tables in Excel and simple formulas.
Don't forget to save Workbook You, Fastest way to save (save) Workbook is with shortcut keys Ctrl + S. Press the button Ctrl and buttons s simultaneously. Give this Excel file a name, for example Employee salary
You can use this Employee Salary Table as a data source create a Mail Merge in Word with Excel data
Hope it is useful