Microsoft Excel: Notes

Notes of Microsoft Excel tips used in real life situations.

CTRL + SHIFT + HOME
CTRL + SHIFT + END
CTRL + SHIFT + UP ARROW
CTRL + SHIFT + DOWN ARROW

Get the value between character(s) or strings

Get the string between "(" and " bytes)", convert to number.

1	A				B
2	41.06 GB (44,086,534,500 bytes) =VALUE(MID(A2,SEARCH("(",A2)+1, SEARCH(" bytes)",A2)-SEARCH("(",A2)-1))

XLOOKUP function

XLOOKUP can replace both HLOOKUP and VLOOKUP functions.

>>> XLOOKUP function

Create custom list to sort data

CANNOT create custom list on Excel 365

>>> Create or delete a custom list for sorting and filling data

LAMBDA function

Use LAMBDA function to create your own function

Courses

Excel for Everyone: Core Foundations | University of British Columbia | edX

References

>>> Microsoft Support - Excel help & learning

MSSQL: Fix error 'Consistency validation for SQL Server registry keys'

While installing Microsof SQL Server 2012, I ran into the following error. "The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys." How to fix Replace the D:\x64\FixSqlRegistryKey_x86.exe file with the original file from the ENU\SQLFULL_ENU.iso file downloading from Microsoft Download Center. This ISO file is 4.2GB. So you can click FixSqlRegistryKey_x86.exe to download this file only. After replacing that file, press Re-run to check. All rules are passed.

Powershell: Check Bad Logon

get-aduser -Filter {BadLogonCount -ne 0} -properties * | select SamAccountName,BadLogonCount,BadPasswordTime,BadPwdCount,@{name='badPasswordTimeDT'; expression={[datetime]::fromFileTime($_.badPasswordTime)}} | sort BadPasswordTime | FT

Powershell: Get-History

Get-History - Gets a list of the commands entered during the current session.

luyện

Search This Blog