Cheatsheet — Excel

This is my personal collection of cheatsheet for Excel.

Numbers

Indian Number Format

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0.00

Approach 2: Format using Rupee currancy with below settings

Hide number when value is zero

[>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##;

Show Negative Numbers in Red Text

1. Select a numeric cell.
2. Select the Conditional Formatting from Home Tab.
3. Select the Highlight Cell Rules .
4. Select the Less Than option.
5. In field Format cells that are LESS THAN: , input 0.
6. Select Red Text from the dropdown and press OK button.

Round Number to Nearest 10

=MROUND(SUM(F:F), 10)

Date

Current Day, Month & Year

=MONTH(TODAY())    // Current month
=YEAR(TODAY())     // Current year
=DAY(TODAY())      // Current day of month. (Select type as number)

DATEIF

• Calculate difference between dates.

=DATEDIF(M4,TODAY(), "M")

Miscellaneous

IF ELSE

=IF(TRUE,4,5)

Last Value of Column

=LOOKUP(2,1/(B:B<>""),B:B)

IFERROR

• If formula can give error, this will help to handle that error.

=IFERROR (value, value_if_error)

COUNT

• Count Number of rows with numeric data.
• Useful on Sr. column.

=COUNT(A:A)

COUNTUNIQUE

• Count Number of rows with Non-numeric data.
• Exclude title row for correct count

=COUNTUNIQUE(B:B)-1

Copy column widths without data

This is the solution to copy column width from one column to another column in another sheet of the same excel. This solution will not change any data. It will only change the width of columns.

1. In the source column, a few rows below the last data row, put some text of desired width ex. Test13464899489494949.
2. Make sure this text is the longest text in the column.
3. Double click on the column separator line. Column width should adjust as per the text entered in step 1.
4. Follow step 1 for the destination column.
5. Follow step 3 for the destination column.

Concatenate Strings in formula

• Use CONCATENATE function

=CONCATENATE('To-do'!B15," & ",'To-do'!B16)

Create dropdown in cell

1. Tested in Google Sheets.
2. Select the cell.
3. Click on “Data” from the main menu. It will show a dropdown with various options.
4. Select “Data Validation”. It will open a pop-up
5. Click on the dropdown of criteria and select “List of Items”.
6. Input comma separated items. It would be shown as a dropdown in the cell.

Excel Shortcuts

1. Strikethrough word(s) → Ctrl + 5