Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)-优快云博客

本文提供了丰富的Excel使用技巧，包括如何将文本转换为数字、基于不同列数据搜索字符串、跨列匹配和返回值、打开多个Excel窗口并排显示、利用数据透视表进行高级数据处理等。还介绍了如何设置打印页面自动添加列标题、调整表格边框、创建数据透视表及优化其布局等实用操作。

Online PDF to Word

1. Word

1.1 Convert Texts into a Table:

2. Visio
2.1 Find & Replace

3. Excel
3.1 Open Excel files in New Window
Lots of times, I will need two Excel windows side by side, on different monitors, so I could work on both at the same. By default, Excel will open Excel files into same Excel Instance and you will have to split window or re-arrange excel file in same monitor to see both files. Here is small trick to change this behavior.

In Excel 2003, go to Tools -> Options -> General tab. Make sure the option, ‘Ignore other applications’ is checked.
In Excel 2007 & 2010, Click the Office button -> Excel Options -> Advanced. Under General, check ‘Ignore other applications that use Dynamic Data Exchange’.

3.2 Formula- Convert a text to Number:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))))

3.3 Formula – Search a Column of Strings Based on Datas in another Column:

**=MATCH(“”&(O6)&””,B:B,0)**

3.4. Matching and Return value crossing different columns 3.4.1
=IF( COUNTIF(‘Servers’!A:A, A3)=0, “No”, “Yes”)
Check if A3 value is in worksheet “Servers” column A. If found , show Yes, else, show No

3.4.2

=VLOOKUP(A3,’Z:\0 Operation\1 Scan\[Scan_Report_Server.xlsx]APP IP’!A:H,8,)
Check if A3 value found in the file “Z:\0 Operation\1 Scan\[Scan_Report_Server.xlsx” – worksheet “APP IP’ – Column A to H. If found, return same row’s , eighth column’s value.

3.5 Pivot Table Tips
3.5.1 Put Multiple Columns into Pivot Table

Pivot-Table-Options-Display-Classic-Pivot-Table-Layout.png?resize=469%2C483&ssl=1

Right Click your pivot table – > PivotTable Options

3.5.2 Do not show subtotal from Pivot Table
After you enabled Classic Pivot Table layout, by default, subtotal will show . Here is how to turn it off:
Step 1. Select a cell in the pivot table
Step 2. On the Ribbon, click the Design tab
Step 3. In the Layout group, click Subtotals, and click Do Not Show Subtotals.