Cheatsheet — Excel

This is my personal collection of cheatsheet for Excel.

Indian Number Format

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

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")

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