Excel

最後更新: 2018-06-28

目錄

  • File extension
  • 16 digit number
  • Excel tick symbol
  • Office 2007 Auto-Save
  • Sort Data
  • revision tracking
  • Cells not updating automatically
  • Single Cell mult line
  • Merge Cell
  • Use Excel Without Mouse
  • To remove duplicate values
  • 用過多字型
  • Excel show sheet list
  • AutoRevover
  • Lock File
  • 受保護的檢視

 


File extension

 

XLSM

XLSM file extension is an Excel Macro-Enabled Workbook file created in Excel 2007 or newer

Just like with XLSX files, Microsoft's XLSM file format uses XML architecture and ZIP compression

to store things like text and formulas into cells that are organized into rows and columns.

 


16 digit number

 

If I type in a 16 digit number (format: number, no decimals) it changes the number on me.

Example: 1234567812345678 changes the view to 1234567812345670.

1.23457E+15

# Each cell can have a maximum 15 digits of precision

解決

Entering the numbers as text. You may type a ' before each number, or change the cell's number format to Text.

 


Excel tick symbol

 

Keyborad

Change the font to *

 * "Wingdings 1"

tick    ALT+0251
cross   ALT+0252

 * "Wingdings 2"

tick      Shife + P

cross    Shife + O

GUI

INSERT -> Symbols -> Font: "Wingding 2"

 


Excel remove cross out (Strikethrough)

 

Right-Click -> Format Cells -> Font tab -> un-tick Effects - "Strikethrough option"

 

 


Office 2007 Auto-Save

 

Microsoft Office Button

Excel Options

Save

Select the Save AutoRecover information every x minutes

 


Sort Data

 

Select the columns you want to sort. For the best results, the columns should have headings.

Click Data > Sort.

Under Column, in theSort by box, select the column you want to sort by.

Under Order, select Custom List.

 


revision tracking

 

* turned on or off on a per-workbook basis

* By default, Excel keeps the change history for 30 days

Turn on change tracking for a workbook

On the "Review" tab, in the "Changes group", click "Share Workbook".

History

 

 


Cells not updating automatically

 

2010 and 2013: File > Options > Formulas > Workbook Calculation > Automatic

 

 


Single Cell mult line

 

Hotkey: Alt + Enter

 


Merge Cell

 

Click Home > Merge & Center

 


Use Excel Without Mouse

 

按 <Alt> 後會show hotkey number

Usage:

<Alt> + H

 


To remove duplicate values

 

"Data tab" -> "Data Tools group" -> "Remove Duplicates command"  (Excel 2013)

 


用過多字型

 

Error

「因為使用過多字型,一些文字的格式將無法正確顯示。
請關閉其他文件,再執行這項作業,以改善這個現象。」

# 解決方案: .xls -> .xlsx

# Office 2013

File -> Export -> Change File Type -> Workbook
 


Excel show sheet list

 

Right-click the worksheet navigation controls (左下) to bring up a new Activate window.
 


AutoRevover

 

Recover crashed Excel from Temporary folder

1.

Follow the temporary file folder location on your PC and find the crashed or unsaved/lost Excel document:

# For Windows 10/8 users:

C:\Users\username\AppData\Local\Microsoft\Office\UnsavedFiles

# For Windows 7 users:

C:\Users\username\AppData\Roaming\Microsoft\Excel\[(.xlsx)Filename_Folder]\Filename((Autosaved-time))

2.

Select the unsaved Excel file which crashed and copy it to Windows PC desktop and save it as a new file.

相關 Setting

FILE -> Options -> Save -> Save AutoRevover information every 10 minutes

建議修改成 auto save 成 "Excel Binary Workbook"

 


Excel Binary Workbook (.xlsb)

 

它是一個 zip 了的 binary file 來 (xlsx 是有個 zip 的 xml)

xlsb 有更好的 performance 及 file size

  • Opens/saves more quickly
  • Smaller file size

  • Binary files have the same RAM memory requirements as other Excel file formats
  • Excel Binary & macro-enabled workbooks may store VBA/macro code, a potential security concern.
  • not in the open standards-based XML file format (OpenXML)

 


Lock File

 

~$orig_filename.docx

This temporary file is called the "owner file"

It is a hidden file.

 

It created in the same folder as the document/template

It is how Word knows that the file is in use and who is using it.

When the document and Word close normally, the file is deleted.

This temporary file holds the logon name of person who opens the file.

 


受保護的檢視

 

1. 按一下 [檔案] > [開啟舊檔]。

2. 在 [開啟舊檔] 對話方塊上,按一下 [開啟] 按鈕旁的箭號。

3. 按一下清單中的 [以受保護的檢視開啟]。

禁用受保護

文件 > 選項 > 信託中心 > 信任中心設置 > 受保護的視圖