Office 2013 Basic Part 2

CHAPTER 5. MICROSOFT EXCEL

5.1. Introduction to Microsoft Excel

Excel is a spreadsheet software that is part of Microsoft's Office suite. Excel makes it easy for users to:

- Calculation and data analysis

- Statistics and reports

- Retrieve data from other databases

- Create statistical graphs and diagrams

- Create macros to automate routine tasks

- And many application features in different fields... Keywords to be familiar with and their meanings:

- Workbook : is a data file with many worksheets

- Worksheet (also called sheet): is a spreadsheet, the current number of rows of a worksheet is 1,048,576 and the number of columns is 16,384.

- Chart sheet : is the sheet containing the graph.

- Ribbon : Groups traditional menu commands into command clusters for easy access on the screen. There are main Ribbon groups: HOME, INSERT, PAGE LAYOUT, FORMULAS, DATA, REVIEW, VIEW.

5.1.1. Components in the Excel window

5.1.2. Data types and input methodsData types

The basic data types commonly used in Excel include: character type, number type, date type. By default, when entering data in Excel, character type will automatically align to the left, number type and date type will align to the right.

- Enter fixed data: click mouse/move mouse with tab key/up key/down key to cell to enter  enter data  enter.

- Enter special characters: Insert  Symbol  select code table  select symbol  Insert

- Enter data using a formula: start with an = or + sign, followed by the data cell address along with operators or functions (functions will be presented in the following section)

Number type

Character type

Date type

Notes :

- If you enter numbers without calculation, you should format them as Text before entering: go to Home  Number  Text  OK.

- Enter date and time: need default format in Control Panel  Regional and Language Options before entering.

- When entering time and want to format it according to 12-hour standard, add A or P after the time entered, otherwise Excel will automatically understand it as AM. For example 6:30 AM or 5:15 PM.

- To enter the same content for multiple cells  select cells  enter data in the first cell  press <Ctrl+Enter>.

a. Cancel input operation

- Press Ctrl-Z: cancel the data just entered

- Press Ctrl-Y: restore the command just canceled

b. Edit cell content

- Edit: Select the cell to edit  press F2  enter new content.

- Delete content: select the cells to delete content  press Delete. This method only deletes data, the cell format remains.

- To clear formatting: select the cells whose content needs to be cleared  Home  Clear  select the option:

 Clear All: clear all content and formatting

 Clear Formats: clears only formats

 Clear Contents: clear only content

 Clear Comments: clear cell comments

 Clear Hyperlinks: delete data links

c. Automatic data entry tool

- Using AutoFill: AutoFill allows automatic data entry according to available templates. AutoFill formats in Excel include:

o Automatically repeat previously entered values. This function is sometimes annoying for users in cases where they do not want to repeat automatically. To enable or disable this function, do the following: File tab  Options  Advanced  select (enable) or deselect (disable) the checkbox Enable AutoComplete for cell values .

o Turn on/off Fill handle: fill handle is an automatically installed function in Excel that allows users to copy formulas.

, data for rows and columns automatically. However,

We can also turn off this function by going to File tab  Options  Advanced  Enable fill handle and cell drag-and-drop.

 Fill data for adjacent row/column: this function allows users to quickly copy data to the next column or row of existing data.

Implementation: select adjacent (blank) column/row of data, select corresponding icon on Home tab  Editing  Fill  select position to fill data: Down/Right/Up/Left.

 In case we want to create a data series automatically, we use

Series . Performed:

 Select the first cell in the range  enter the first value of the range

INPUT VALUE

EXTENDED VALUE
1, 2, 3	4, 5, 6,...
9:00	10:00, 11:00, 12:00,...
Mon	Tue, Wed, Thu,...
Monday	Tuesday, Wednesday, Thursday,...
Jan	Feb, Mar, Apr,...
Jan, Apr	Jul, Oct, Jan,...
Jan-07, Apr-07	Jul-07, Oct-07, Jan-08,...
15-Jan, 15-Apr	15-Jul, 15-Oct,...
2007, 2008	2009, 2010, 2011,...
1-Jan, 1-Mar	1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3)	Qtr4, Qtr1, Qtr2,...
text1, textA	text2, textA, text3, textA,...
1st Period	2nd Period, 3rd Period,...
Product 1	Product 2, Product 3,...

Maybe you are interested!

 Home  Editing  Fill  Series  screen appears

 Linear : creates a calculated range by adding the value to each successive cell in the Step value.

 Growth: creates a calculation sequence by multiplying the value for each successive cell by the value in Step value .

 Date : creates an ascending date series according to the value specified in Step Value and the unit in Date unit.

 AutoFill : create a series of identical data by moving the Fill handle on the spreadsheet.

o Automatically fill in numbers, dates, months, years and other formatted character strings: this function allows the user to enter a series of values in ascending order of a known series of values. The following table gives suggestions of input values corresponding to the extended values.

5.2. Worksheet management

5.2.1. Operations on worksheet

a. Add a worksheet

- Click Insert Worksheet on the right button of the current worksheet

- Or R_Click on the sheet name, select Insert  the Insert dialog box appears  select Worksheet.

b. Delete worksheet

- R_Click on the worksheet tab to delete  Delete

c. Rename worksheet

- R_Click on the worksheet that needs to be renamed  select Rename

 type new name  enter

- Or double_click on the worksheet  type new name  enter.

d. Copy worksheet

- Select the worksheet to operate  select Move or Copy, the screen appears. Perform the operations from 1 to 3 as shown and press OK.

- If you want to move the worksheet  perform steps 1 and 2 then click OK.

1 . Select the file name containing the worksheet after copying. By default

set to the current workbook name

2. Select the location to place the worksheet

3. Select Create a copy if you want to create a copy table on the same file.

e. Managing content on spreadsheets

- Select the entire spreadsheet content: click on the top corner of the spreadsheet (left of column A and above row 1).

- Select row/column: click on row name (eg: 1) / column name you want to select (eg: A)

- Change row/column width to fit content: move mouse to top/bottom edge of row or left/right edge of column then double click.

f. Find and replace data

- Click Find & Select on the Editing group of the Home tab

- Select Find or Replace, a dialog box appears

1. Enter the content you want to search for

2. Enter the content to replace

5.2.2. Print Worksheet or Workbook

A workbook can contain many separate data worksheets. We can print all or part of the worksheets, or any selected worksheet or all worksheets with content at the same time. By default, Excel only prints the current Worksheet. To print, select File  Print or press Ctrl + P, the dialog box appears. Follow these instructions:

- Select the parameters as shown and press the Print button.

- If you want to set up other formats for the printed page such as header/footer or page title, etc., click Page Setup to customize.

5.2.3. Set options in Page Setup

- Ribbon  Page Layout  Page Setup, the screen appears