Excel Basics

1. Ribbon

Be familiar with the tool bar.

2. Navigation

fn+左箭头: = home
fn+左箭头+ctrl: 回到文档行首
fn+右箭头+ctrl: 回到文档尾部
fn+上/下箭头: 上下翻页page up/down

3. Selection

cmd: 选择不连续的行/列

4. View

view: 三种模式：Normal, Page break preview, Page layout.

zoom to 100%

show/hide Formular bar/Headings/Gridlines

split/freeze

switch windows: windows are new workbooks, not the sheet.

4. Edit

Delete in the HOME tab: delete an entire cell and it will affect the surrounding cells
delete key in the keyboardwithout affecting the surrounding, just clearing the contents of a cell.

5. Fill

double click when blod black cross turns out:

• Number: copy by default.
• Text: copy by default.
• Text+Number: series in Number by default.
• Date: series by default. E.g. Tuesday, 01/01/2020
• Flash fill: fill in a pattern
  

6. Common used keyboard shortcuts

cmd+Z/cmd+Y: undo/redo
fn+左箭头: = home
fn+左箭头+ctrl: 回到文档行首
fn+右箭头+ctrl: 回到文档尾部
fn+上/下箭头: 上下翻页page up/down
cmd+O/N: New/Open workbook
cmd+shit+right arrow/down arrow: 选中某行/列，选中右边/下边全部，右键点击隐藏。
cmd+ PgUp(fn+up/down)— Go to the previous/next sheet.
cmd+B: change to bold text.
ctrl+shift plus +plus -: insert.
ctrl+shift+下箭头/右箭头: 选中单元格到列尾/行尾
cmd+P: Open Print dialogue

7. Function

• How to use function: MAX,AVERAGE, MAX,MIN.
• Absolute reference (cmd+T): $B$1.
• Relative reference: B1
• Calculations across sheet：
  

8. Format

• How to use Format painter/clear format.
• How to create banded rows:

They can be set up by manually filling the background of one row and then select that row and the next one with no fill. Now you can use the format painter and drag those rows in which you want to see the banded rows.

• Number formatting

Currency format is different from accounting format. In the accounting, the currency symbol is left aligned, away from the number.

• How to use Cell Style.
  
• How to change to a Theme.

Change the theme for the workbook, not just the worksheet. However, this applies only to the cells that you have previous formatted with theme styles. As soon as you have used standard or custom formats, applying a theme will not change those cells.

9. Find and replace

In Mac, you can use find next, but we can replace all.

10. Filtering

• Filter by one cell value (cell colour and so on)
  

• Filter by multiple values

11. Sorting

Note: Don’t select any columns before sorting. Instead clicking the Filter
button firstly, and then click the Sort
button.

12. Conditional formatting

One way to highlight according to a condition.

Also, we can use it as a simple visualisation for our data.

13. Print

A practical way to try this is to use pdfto preview instead of printing them out.

Page Layout View

• In Mac, set the Print Area, and then click Print/cmp+P. All these options are in File/page LayoutTab. Or you could use cmp+P, and choose the Selectionto print what you exactly want.

• You can change the orientation, margin, Paper size and so on.
• You can fit your data into 1/2… page width/length.
  

Page break

• How to add/clear a page break.
  

Print titles
You can add column and row heading for each page.

Print headers and footers
Click the Add header/footer. you add add page number, author name, date, file name/path and so on.

14. Chart

• How to create a chart. Commonly used ones: pie chart, bar chart and line_chart.
• We can change the data displaying in the chart by clicking the chart and drag the box of data.
• We can copy our chart to other sheet or create a chart sheet.
  
• You can share the chart in other applications. You can embed your chart or link the chart. Linking the chart means that the data change will be automatically updated in the destination. In addition, you can choose to fit the destination style or original one.