Friday, February 1, 2019

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


1. Word

1.1 Convert Texts into a Table:
 






2. Visio
2.1 Find & Replace

3. PDF

Online PDF to Word

4. Outlook
4.1 Add a new share email box into outlook




5. Excel
5.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’.



5.2 Formula- Convert a text to Number:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

5.3 Formula - Search a Column of Strings Based on Datas in another Column:

=MATCH("*"&(O6)&"*",B:B,0)


5.4. Matching and Return value crossing different columns 
5.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

5.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.


5.5 Pivot Table Tips
5.5.1 Put Multiple Columns into Pivot Table
Right Click your pivot table - > PivotTable Options

5.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.




5.5.3 Change PivotTable Column Name
click to select the column name, press F2

5.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.




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

 

Set Tables Border:
 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 



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





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

1 comment:

  1. Excel is a software program from Microsoft basic excel for beginners that is a part of Microsoft Office. Excel calculations is compiled for making and altering spreadsheets that are spared with a .xls expansion. It's general uses in corporate cell-based figuring, turn tables, and different diagramming devices. With an Excel spreadsheet, you could make a month to month spending plan, track costs of doing business, or sort and compose a lot of information calculations in spreadsheets of Excels.

    ReplyDelete