Online PDF to Word

1. Word

1.1 Convert Texts into a Table:

1.gif

1.gif 

2. Visio
2.1 Find & Replace

288836288836


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’.
fOQEG.png?resize=320%2C200fOQEG.png?resize=320%2C200




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=1Pivot-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.

pivot2007nosubtotals.gif?resize=293%2C217&ssl=1pivot2007nosubtotals.gif?resize=293%2C217&ssl=1

3.5.3 Change PivotTable Column Name

click to select the column name, press F2
3.5.4 Group Multiple Rows to one
Hold ctrl key to select the rows you want to group, then right click to select group. You also can use F2 to change grouped name.

3.6 All Kinds of Excel GIFs
Automatically Add Column Titles on Each Print Page:

5.gif5.gif 

Set Tables Border:

6.gif

6.gif 


7.gif7.gif 


8.gif8.gif 


9.gif9.gif 


10.gif10.gif 


11.gif11.gif 


12.gif12.gif 


13.gif13.gif 


14.gif14.gif 


15.gif15.gif 


16.gif16.gif 


17.gif17.gif 


18.gif18.gif 


19.gif19.gif 


20.gif20.gif 


21.gif21.gif 


22.gif22.gif 


23.gif23.gif 


24.gif24.gif 


25.gif25.gif 


26.gif26.gif 


27.gif27.gif 


28.gif28.gif 


29.gif29.gif 


30.gif30.gif 


31.gif31.gif 


32.gif32.gif 


33.gif33.gif 


34.gif34.gif 


35.gif35.gif 


36.gif36.gif 


37.gif37.gif 


38.gif38.gif 


39.gif39.gif 


40.gif40.gif 


41.gif41.gif 


42.gif42.gif 


43.gif43.gif 


44.gif44.gif 


45.gif45.gif 


46.gif46.gif 


47.gif47.gif 


48.gif48.gif 


49.gif49.gif 


50.gif50.gif 


51.gif51.gif 


52.gif52.gif 


53.gif53.gif 


54.gif54.gif 


55.gif55.gif 


56.gif56.gif 


57.gif57.gif 


58.gif58.gif 


59.gif59.gif 


60.gif60.gif 


61.gif61.gif 


62.gif62.gif 


63.gif63.gif 


64.gif64.gif 


65.gif65.gif 


66.gif66.gif 


67.gif67.gif 


68.gif68.gif 


69.gif69.gif 
用数据透视表做分组计数:
be26d555gw1er0al27ox6g20b407e1kx.gifbe26d555gw1er0al27ox6g20b407e1kx.gif

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