Microsoft Excel Tips and Tricks - NETSEC


Learning, Sharing, Creating

Cybersecurity Memo

Wednesday, March 3, 2021

Microsoft Excel Tips and Tricks

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. 

Remove Duplicates

Keyboard shortcut: Alt+A+M

This brings up the Remove Duplicates window where we can select which column(s) we want Excel to remove duplicates from.

Excel Remove Duplicates Feature

Sum for certain month based on conditions

RAW Data :

Sum for each month and also for each type:

E2==SUMIFS(Details!D:D,Details!A:A,">="&Monthly!B2,Details!A:A,"<"&Monthly!B3,Details!B:B,"="&"Good Mail")

F2=SUMIFS(Details!D:D,Details!A:A,">="&Monthly!B2,Details!A:A,"<"&Monthly!B3,Details!B:B,"="&"Edge Block Spam")

Count Word Appear Percentage

Enter this formula: =COUNTIF(B2:B15,"Yes")/COUNTA(B2:B15) into a blank cell where you want to get the result, and then press Enter to a decimal number, see screenshot:

doc per of yes 1

Some other examples:

After this, you might need to change cell format to percentage.

Transpose Every 5 Or N Rows to Columns

In Excel, you can apply the following formula to transpose every n rows from one column to multiple columns, please do as follows:

1. Enter the following formula into a blank cell where you want to put the result, C1, for example, =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)), see screenshot:

doc transpose every 5 rows 2

Note: In the above formula, A:A is the column reference that you want to transpose, and A1 is the first cell of the used column, the number 5 indicates the number of columns that your data will locate, you can change them to your need. And the first cell of the list must be located at the first row in the worksheet.

2. Then drag the fill handle right to five cells, and go on dragging the fill handle down to the range of cells until displays 0 , see screenshot:

doc transpose every 5 rows 3

Copy Web Page Data into Excel

Copy Web Page Data into Excel with Proper data format change. Remove Empty Rows.

For example, copying following page into excel is a mess. 

Here is what I did:
a. Copy selected data into notepad 
b. use replace feature to remove no-need data, such as 'add' this kind of texts.
c. copy left data into Excel. It might still have lots of empty lines with spaces in your data which you want to remove.
d. use =trim function to remove spaces in the empty cell
e. copy the trim function line.
f. choose the first cell of your copied data, press f10, which give you option to only copy text to overwrite existing data
g. Using find&Select button , select Go To Special...
h. select blanks, which will select all blank cells. If there is a space in, that cell will not be selected.
i. Select Delete button, then choose Delete sheet Row, this will delete those Empty rows does not have data.

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

Formula- Convert a text to Number


Search a Column of Strings Based on Data in another Column


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

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

Pivot Table Tips

1 Put Multiple Columns into Pivot Table
Right Click your pivot table - > PivotTable Options

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.

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

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.

Excel GIFs

Automatically Add Column Titles on Each Print Page:

Set Tables Border:







































































1.选取B列区域,开始 - 条件格式 - 新建规则


  • 规则类型:默认
  • 格式样式:图标集
  • 图标样式:黑红黄绿4按钮格式
  • 设置区间值:根据需要填写间隔值
  • 类型:默认是百分比,这里改成数字。




会动的图表 Excel让数据开始说话!























No comments:

Post a Comment