Skip to main content

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. 

Little Savings - Excel


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

Little Savings - Excel

Little Savings - Excel


This is the output after delimitng any character in a row.
Little Savings - Excel



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.

Little Savings - Excel


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

Pivot Table
Pivot Table
Pivot Table

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.

Little Savings - Excel

Output:
Little Savings - Excel

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.
Little Savings - Excel



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


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

LittleSavings - MSEXCEL

LittleSavings - MSEXCEL

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.

Workday

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

Transpose

Transpose




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

Comments

  1. Learning shortcut keys is key to master Excel and improve work productivity. User guides at www.Office.Com/Setup covers all this and many more. Thank you for sharing this.

    ReplyDelete

Post a comment