Microsoft MS Excel Shortcuts & Excel functions

# Little Savings

MS Excel has become common now-a-days, it not only helps to make our work simple, it has been widely used by other professionals.

But to make it better, one should also know the Excel shortcuts and How to use the different excel functions available.

I tried to collate the most used excel functions which helpful for software professionals, let's have a look.

Excel Shortcuts:

Text To Columns - To separate the special chars from Text

Select Blank Fields - To highlight the blank fields/cells

Conditional Formatting - Coloring the fields based on conditions

Excel Functions:

Countif - Counting the text present with single condition

Countifs- Counting the text present with multiple conditions

Workday - To find the working days from today

Concatenate - Collate/Club the text from two cells

Transpose - Change data from horizontal rows to vertical columns or vice-versa.

In detail, 

Text to Columns:

When we copy - paste something into the excel. Suppose, you copy the below text into excel.

1. User app crashed while login

2. User unable to login.

It will paste as below. 

If you want to separate the serial number and text, use the shortcut "Text to Columns".

Steps to follow-

• Select the cells you want to segregate
• Select Data tab -> Find Text to Columns.
• Select the Delimited radio option
• Click Next
• Select the corresponding de-limiter based on your requirement
• Click Finish

This is the output after delimitng any character in a row.

Select the blank fields:

While dealing with the multiple columns in an excel sheet, it is always to select the blanks spaces at different places.

In Microsoft excel, it is easy to highlight the blank spaces by using the following way.

Steps to follow:

• Select the whole sheet
• Go to Find & Select under Home tab
• Select to Go To Special
• Select Blanks
• Now you can edit /delete the fields by selecting the Delete option from Home page.

Note that it will delete the rows/columns with content too, while deleting the blank spaces.

Pivot Table:

Pivot table in Excel is often most used to get a clear picture on the status by organizing the data.

Of course, this has been know by many individuals, posting for who are new to it.

Steps to Follow:

• Select the whole sheet
• Navigate to the Insert -> Select Pivot Table -> Click Ok on the pop-up
• Your pivot table is ready.
• Now you can play around by dragging and drop into columns and rows(present on right side).

Conditional Formatting:

Conditional formatting will help to highlight the cells if specific condition has met. Suppose, if you want to highlight the amounts less than 10000 in a column with one color and equals to 10000 with another color, conditional formatting will help to highlight those cells.

Steps:

• Select the rows.
• Go to Conditional Formatting under Home tab
• Select "highlight cell rows"
• Select the condition.

Manage Rules:

If you want to see the existing rules, go to Manage Rules under conditional formatting.

Output:

Count Text:

If you want to count, no. of times the text has been repeated in a row/column, the following function will be helpful.

Function Name - COUNTIF(Range, "Text")

In the below example, based on the no of times, the Yes has been repeated, the ponts to be counted. So, the function "CountIf" will be useful.

Count Text with Multiple Conditions:

In the above example one can fund that the CountIF comes with a single condition to count the text repeated within a range. 

But the COUNTIFS, one can write multiple conditions to find the text.

From the given screenshot, one can get the no of defects meet both the conditions 

Status with Fixed

Priority with Blocker

Syntax: =COUNTIFS(Range1, "text1", Range2, "text2"...)

In the above example one can fund that the CountIF comes with a single condition to count the text repeated within a range. 

But the COUNTIFS, one can write multiple conditions to find the text.

From the given screenshot, one can get the no of defects meet both the conditions 

Status with Fixed

Priority with Blocker

Syntax: =COUNTIFS(Range1, "text1", Range2, "text2"...)

Concatenate:

Concatenate excel function helps to club the data of different cells into single cell.

Syntax: =CONCATENATE(text1, text2, text3...)

Steps to follow:

• Find the cells you want to concatenate
• Write the function for concatenate(col_1, col_2, col_3)

Tip : Add space in each cell of the text to get the same in clubbed cell

Workday:

If you want to project the future date excluding weekends & holidays this excel function will help you. 

Syntax: =WORKDAY(start_date, days)

Arguments:

If you have given the start_date(today or future) and no of days, it will give the future date excluding weekends and holidays.

Transpose:

We may thought to change all the horizontal data to vertical. Transpose is the one of the function useful for it.

Syntax : TRANSPOSE(array)

Steps to follow:

• Select the exact blank cells to change from vertical to horizontal or vice-versa.
• With the blank cells selected, write syntax TRANSPOSE(select the data you want to change)
• Press CTRL + SFT + ENTER

Other Excel functions for the knowledge

CHOOSE:

To choose any one value from the given list.

Syntax : CHOOSE(value to choose, value1, value2, value3)

Eg., Choose(2, "little", "savings", "co.in")

Output - savings

COLUMN:

This excel function gives the column number of the cell which has passed to it.

Syntax : Column(C5)

Output : 3

Columns:

This function will give the no of columns present for the given range.

Syntax: Columns(A1:F1)

Output : 6

Counta:

This function helps to find the no of cells that contains text.

Syntax : COUNTA(V1, V2, V3)

Output : 3(if all the cells contain text)

Day:

Day excel function returns the day of the date has been passed to it.

Syntax : DAY(date)

Today:

Today excel function returns current date(only date)

Syntax : TODAY()

Note : No arts required.

Now:

This function will returns both current date and time.

Syntax : NOW()

DAYS:

The days excel function helps to find the no of days between start and end dates given.

Syntax : DAYS(end_date, start_date)

Network Days:

The NETWORKDAYS excel function will help to get the working dates between the given two dates.

Syntax : NETWORKDAYS(date2, date1)

This function is similar to the Workday, only the syntax is different.

You can extend your queries with the 

NETWORKDAYS.INTL, WORKDAY.INTL excel functions.

Exact:

The exact function compares two strings. 

Syntax : Exact(text1, text2) 

Find:

Find excel function will get the location of a string. Note this is case-sensitive. 

Syntax : FIND(find_text, within_text) 

Search:

The search excel function will serves the same as Find function, but this is not case sensitive. 

Syntax : Search(find_text, within_text) 

MATCH:

This excel function helps to get the position from the given array.

Syntax: MATCH(lookup value, array)

REPLACE:

The replace excel function helps to replace the text based on the location.

Syntax: REPLACE(old_text, start_num, num_chars, new_text)

SUBSTITUTE:

The substitute excel function also helps to replace the text but instead of location, this can substituted with old and new one.

Syntax : SUBSTITUTE

IFERROR:

This excel function mostly used for if you want to display the specific text when the condition throws error. 

Syntax : IFERROR(value, value_if_error) 

iFNA: 

The IFNA will be helpful when the value is not available, can show the requires text. 

Syntax : IFNA(value, value_if_na) 

HLOOKUP, VLOOKUP, LOOKUP:

All the lookup excel functions will help to match the given value in the given column from the array. 

HLOOKUP will help for search in horizontal rows. 

VLOOKUP will help to search in vertical columns. 

LOOKUP can use for both and only one at a time. 

Syntax:

HLOOKUP(value, table, row_index) 

VLOOKUP(value, table, col_index) 

LOOKUP(value, one row/col) 

INDEX:

This excel function will also help to get the value of the given position(row & column) from the given array.

Syntax :

INDEX(array, row_no, col_no)

ISBLANK: 

This excexcel function helps to find whether the cell is empty. 

Syntax: ISBLANK(value) 

ISERR:

This excel function checks for any error except #NA.

Syntax : ISERR(value) 

ISNA:

This is to check for #NA error. 

Syntax: ISNA(value) 

ISNONTEXT:

This is to verify whether the cell value is a not a text. 

ISNONTEXT(value) 

ISNUMBER: 

This is to check whether the cell value has a number. 

Syntax : ISNUMBER(value) 

ISTEXT: 

This is to check whether the cell value has a text or not. 

Syntax : ISTEXT(value) 

LEFT:

This excel function will help to extract the text from left side of the string. It will pick the given no of characters from left side.

Syntax : LEFT(Text, no of chars)

RIGHT:

This excel function will help to extract the text from right side of the string. It will pick the no of characters from right side.

Syntax: RIGHT(text, no of chars)

LEN:

This excel function will helps to get the length of the string/text.

Syntax : LEN(text)

LOWER:

This function helps to convert all the text into lower case.

Syntax : LOWER(text)

Eg: LOWER(LITTLE)

Output : little

UPPER:

This function helps to convert all the text into proper case.

Syntax: UPPER(savings)

Output : SAVINGS

PROPER:

This function helps to convert into camel case.

Syntax: PROPER(text)

Eg. PROPER(littlesavings)

Output : Littlesavings

MIN, MAX, MINA, MAXA:

The min and mx excel functions finds the min and Max number from the given set of numbers. These function ignores any empty value in the given list.

Syntax:

MIN(N1, n2, n3..)

MAX(N1, n2, n3..)

The MINA, MAXA excel function also behave as MIN and MAX, but the value TRUE/FALSE considered as 1/0.

Syntax:

MINA(N1, n2, n3..)

MAXA(N1, n2, n3..)

NOT:

This excel function will reverse the results.

Syntax : NOT(result)

REPT:

This excel function helps to add the same text again till the given no of times.

Syntax: REPT(text, no_of_times)

ROW:

This function helps to get the row no of a reference.

Syntax: ROW([ref])

ROWS:

This function helps to get the no of rows in an array.

Syntax: ROWS(array)

SHEET:

This function helps to get the index no of the sheet

Syntax: SHEET(value)

SHEETS:

This function helps to get the no of sheets in a ref.

Syntax: SHEETS(ref).

PRODUCT :

To get the product of the supplied numbers.

Syntax: PRODUCT(N1, N2)

SMALL:

This excel function helps to find the nth smallest number from the given array.

Syntax: SMALL(array, n)

SUM, SUMIF, SUMIFS:

We all know about the SUM excel function, which helps to add the numbers. The SUMIF, SUMIFS excel functions will help to add based on the single condition and multiple conditions respectively.

Syntax:

SUM(A1, A2, A3....An)

SUMIF(range, criteria, [sum range])

SUMIFS(range, criteria, [sum range])

T:

The T excel function will filter the text values alone.

Syntax: T(value)

Text:

The text excel function will helps to change the format.

Syntax: TEXT(value, format_text)

Text(0.85, "0%")

Output : 85%

TRIM:

The trim excel function helps to remove the extra spaces from the text.

Syntax: Trim(text)

TRUNC:

The trunc functions helps to truncate the number to the given no of digits.

Syntax: TRUNC(number, no of digits)