Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)

Online PDF to Word

• Convert PDF to Word (Free!)
• SmallPDF

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 All Kinds of Excel GIFs
Automatically Add Column Titles on Each Print Page:

 

Set Tables Border:

 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 

Notes: Download Link for those animated gif : magnet:?xt=urn:btih:99D83AFC3C9A8BD5D02F79A0071A921A7CCFDA19&dn=%E4%B8%80%E5%A5%97%E4%BB%B7%E5%80%BC8800%E5%85%83%E7%9A%84excel%E6%95%99%E7%A8%8B.rar

用数据透视表做分组计数:

Reference:
1. Excel Tips Net
2. Excel Formula : Convert a text to Number
3. Excel Formula: Search a column of strings in the spreadsheet