The ways in which one can use Excel is countless. Its varied list of exciting features is beautifully packed inside Microsoft’s most popular number crunching application. After Microsoft came up with 2010 version, lots of new and exciting features got added, which was a great bonus. Whether you are a casual user or an expert one, there are always new things to learn. Here are 10 tips and tricks that will unleash your efficiency, making you work smarter and not just harder. Let’s dive straight into it.
Inserting multiple rows or columns in a single go
Many of us know the way to add a single row or a column at a time, but using this technique becomes a tedious process if you need to insert multiple rows and columns. To smartly do that, just drag and select N rows or columns (where N is two or more), if you want to add N rows or columns above or left. Just do a right click over the highlighted rows or columns and select ‘Insert’ in the drop down menu (the image below shows how to insert two new rows). By doing so, you have quickly inserted multiple rows or column in barely two steps. Isn’t that great!
Transpose data from row to column or vice-versa
If you need to transpose data from row to column or vice versa, re-entering all the data values again into the spreadsheet would be a dull and a monotonous idea. You can save yourself from all this just by using this transpose feature. This is how it can be done easily: just select and copy the area you want to transpose, place the pointer to some other blank cell. Go to Home (Clipboard Section) –>Paste –> click on Transpose in the drop-down menu. Take note that this function would not activate if you haven’t copied the table content.
Expedite inputting complicated terms via Autocorrect
When one has to enter complex or a long data value repetitively, using AutoCorrect function can be a handy option, which replaces your short defined values with the correct text. For instance, say that you want to save yourself from typing out “Elon Musk” by using the shorthand “EM.” Therefore, every time you input EM, it will autocorrect to ‘Elon Musk’. To use the feature just go to File –> Excel Options –> Proofing –> AutoCorrect Options and define the replace text and the correct text in the red rectangular area and finally click on Add button. Great! your job is done.
Split text in a cell to multiple columns
Suppose you need to split the information stored in a single cell into different columns. For instance, if you have to get first name and last name in different columns. One boring way could be cutting a part of text and pasting it to next cell, but that could be really most time consuming and boring way to do that.
Thanks to excel, you could automate this work. Through Excel’s text to column feature, this tedious task can be done in just seconds. To do that, just select the desired column data which you want to split. Then directly head to the Data tab and click on “Text to Columns.” A 3-step wizard will pop up, requiring some additional information.
- Step 1: Choose ‘Delimited’ to split up the column data, on the basis of delimiters such as commas, tabs, whitespace etc. or choose ‘Fixed width’ to split the column, from an exact location, by specifying a location of place from where you want split to occur. In the example below, we have selected ‘Delimited’ so we can split our column containing the full name to a column having the first name and a column with the last name.
- Step 2: Select the delimiter as space.
- Step 3: Choose the desired data format of each column with its preview.
When satisfied, hit finish to complete the process.
Restricting wrong inputs via data validation function
To maintain the authenticity and integrity of data, sometimes you need to validate and restrict various input values and even offer some useful messages, in cases of discrepancy.
For instance, if you need to take in details of the age of students, you need to ensure two things: that age should be a whole number and should lie between in specified range. In this case, suppose 21-35. To keep a check on these two broad conditions, just head to Data tab select Data Validation. A popup window will appear. In Setting tab, put these following conditions and you can even give prompts in the event of discrepancy like, “Please input your age as a whole number, which should range from 21 to 35” by defining it in ‘Input Message’ tab. Users will get this prompt when hovering the mouse pointer in this area and receive a warning message if the entered information is not in accordance with the specified condition.
Navigate promptly by pressing Ctrl + desired Arrow key
To jump straight to the edge of the sheet, in different directions, simply click Ctrl key + the desired arrow key button. For instance, if you need to jump to the top line, of the data table, just press click Ctrl + upward arrow button and there you are.
Rename the worksheet by just double clicking
You could rename a sheet by right-clicking on it and selecting Rename. The smart and quicker way is to just double-click on the desired worksheet and rename it directly.
Create values by combining values of different cells via ‘&’
Databases try to split out data to make it as exact as possible. For instance, instead of having data that shows a person’s full name, it might contain the data as a first name and then a last name separately in two columns. In Excel, you can combine cells having different data into a single cell, simply by using “&” sign in your function.
In the example below, First name is concatenated with Last Name (with space ” ” in between) by selecting the respective cell in the formula with “&” operatoThe functionion is then copied on to subsequent cells below.
Transforming the text case
There are some simple and easy formulations which can make you change text case quickly. UPPER, LOWER and PROPER are few of those predefined functions, which can help you transform text as per your different requirements. Let’s briefly understand these simple functions.
Upper function lets you capitalize all the characters of the text. Lower function, on the other hand, transforms the text into lower case. Proper function is a little different as it capitalizes only the first character of a word.
One click to get more status of data
Most of us are aware of checking the data status, in the bottom of an Excel sheet, like Average, Count and Sum Value. However, a simple right click on this bottom status area could enable you to view additional figures which includes numerical count, maximum, minimum and many more, as shown in the picture below.
Well! Those were 10 cool, handy tips and tricks, which you should definitely try out to get quicker results in a smarter way.