首頁 » office

Excel

由 datahunter 在 一, 22/12/2014 - 13:22 發表

最後更新: 2023-09-28

目錄

  • Auto Fit Column Width
  • To remove the first 3 characters from a whole column
  • Leading zeros
  • To highlight the cell when it value is duplicate in column
  • 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
  • 受保護的檢視
  • Filter
  • Delete row based on cell value
  • SUM
  • Array formula
  • ROUNDUP
  • unix timestamp to date
  • Apply formula to an entire column or row without dragging by Fill feature
  • remove duplicate
  • To apply color to alternate rows
  • delimiter
  • "grey lines" are missing
  • Show sheet tabs
  • Colume show '#UNKNOWN!'
  • $A:$A
  • Hotkey

 

Auto Fit Column Width

 

Home > Cells > Format > AutoFit Column Width

 * 對數字無效, 因為它會轉成類似 "1E+10" 格式

 * 當使用了 Wrap Text" 時亦無效, 因為它會在 Cell 內自動分了多行

 

To remove the first 3 characters from a whole column

 

在 B1 使用 function "=RIGHT(A1, LEN(A1)-3)"

Copy 它在 C1 Past "Value" (ctrl+v)

 

Leading zeros

 

Select the range of cells
 > Right-click on the selected cell(s) and choose "Format Cells"
 > In the "Category" list, select "Custom"
 > In the "Category" list, select "Custom"
 > In the "Type" field, enter the desired format: "000"

 

To highlight the cell when it value is duplicate in column

 

Select the column where you want to check for duplicates
> Home > Conditional Formatting
> "Highlight Cells Rules" > "Duplicate Values"

 

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 the Sort 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. 按一下清單中的 [以受保護的檢視開啟]。

禁用受保護

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

 

Filter

 

Turn Filters On or Off

[方式1]

Data > Filter

[方式2]

Hotkey: Ctrl+Shift+L

Notes

  • 左下角會有 "N of M records found"

 

Delete row based on cell value

 

By Filter

 

SUM

 

=SUM(B2:B30)              # It ignore text row

當 Colume 內的 Vaule 有 Unit 時, 就要用 Array Formula

 

Array formula

 

應用: Sum 的 Value 有 Unit. ie. 600 MB

=SUM(SUBSTITUTE(B2:B30, " MB", "") + 0)
CTRL+SHIFT+ENTER

CTRL+SHIFT+ENTER => array formula

Curly brackets '{}' will automatically appear at the start and end of the formula.

* Every time you edit an Array Formula, you must remember to press Ctrl+Shift+Enter afterward.

 

ROUNDUP

 

=ROUNDUP(12345.6789, 2)     12345.68

=ROUNDUP(12345.6789, 1)     12345.7

=ROUNDUP(12345.6789, 0)     12346

=ROUNDUP(12345.6789, -1)     12350

=ROUNDUP(12345.6789, -2)     12400

ROUNDDOWN

ROUND                                    # 四捨五入

 

unix timestamp to date

 

1. Generic formula

=A2/(24*60*60)+DATE(1970,1,1)

OR

=A2/86400+25569

2. Change the cell format to Time format

right-click cell > Format Cells > Date to Time format

 

Apply formula to an entire column or row without dragging by Fill feature

 

Home > Fill > Down

 

Remove duplicate

 

1. Select the range of cells that has duplicate values you want to remove

2. Click Data > Remove Duplicates

 

To apply color to alternate rows

 

1. Select a range.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click New Rule.

4. Select 'Use a formula to determine which cells to format'.

5. Enter the formula =MOD(ROW(),2)

6. Select a formatting style and click OK.

 

How to open CSV files with the correct delimiter/separator (semicolons (;) or commas (,))

 

Excel 2016

Menu > Data tab > Get External Data > "From Text" > Select your CSV file

 

"grey lines" are missing

 

原因: The cell is filled with the white color

Fix: Select that cell > At the top (the ribbon) > Home tab > click on Fill Color drop down > select "No Fill"

 

Show sheet tabs

 

File > Options > Advanced > Display options for this workbook "Book1"

  • Show horizontal scroll bar
  • Show sheet tabs option
  • ...

 

 

Colume show '#UNKNOWN!'

 

click '#UNKNOWN!' Col 會出 "unavailability data type"

 

 

$A:$A

 

$A:$A 的意思是引用整個A列, 而且不管怎樣拖動公式, A列都不會變化

 

Hotkey

 

  • 新增一行                      Ctrl-Shift-Plus
»
  • 瀏覽次數： 3113