Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I'd ever need to do manually can be done automatically. Need to merge two sheets with similar data? Excel can do it. Need to do simple math? Excel can do it. Need to combine information in multiple cells? Excel can do it. In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel tricks to get you started with how to use Excel.
Copy Web Page Data into Excel
For example, copying following page into excel is a mess.
Here is what I did:
h. select blanks, which will select all blank cells. If there is a space in, that cell will not be selected.
Open Excel files in New Window
- 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’.
Formula- Convert a text to Number
Search a Column of Strings Based on Datas in another Column
=MATCH("*"&(O6)&"*",B:B,0)
Matching and Return value crossing different columns
=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
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.
Pivot Table Tips
1 Put Multiple Columns into Pivot Table2 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.
3 Change PivotTable Column Name
Excel GIFs
Automatically Add Column Titles on Each Print Page:
Set Tables Border:
用数据透视表做分组计数
Reference:
via Blogger https://ift.tt/2OkeEVP
March 03, 2021 at 09:53AM Software