Home > Blogs > Seven Tips to Become a Pro at Excel
Excel is probably the most underrated professional tools available in the market today. Very few can boast of having mastered Excel. Those who are working towards it or have a good knowledge of the software certainly make an impact on their team mates.
There are endless online Excel tutorials to help you hone your Excel skills. You can also opt for the Advanced Excel Training programs offered by Manipal ProLearn to improve your professional efficacy and in turn, your productivity.
There is always something new to learn in Excel and below are some of the quick tips that could save you time and energy.
1. Auto Fill – Auto Fill can save you a ton of time taken on menial typing. It is a handy tip to use when you are filling in a series of repetitive entries like dates, numbers or alphabets in consecutive cells.
All you need to do is begin the series and then move the cursor to the bottom left corner of the last cell of the series using the fill handle. Use the little plus sign (+) and click and drag to select all the cells you need to fill, and voila – time saved.
2. Text to columns – When working with a data dump in Excel, you might need to separate text into different columns for ease of use. For example, if you have a column with full names, but want to separate it out to have two columns with first name and the second with last name or if you have a numerical code and then a word, you can use ‘Text to Columns’ feature to separate it out. Select the data, then on the ‘Data’ tab click ‘Text to Columns’, then choose to separate it either with delimited or fixed width.
3. Freeze Rows and Columns – When you have lengthy spreadsheets, the header disappears as you scroll down leaving you with data that you have lost track of. The easy way to keep the data visible is to freeze panes, which is a feature available under the ‘View’ menu. Just highlight the below or to the right of the row or columns you want to freeze and choose freeze panes.
4. Remove duplicates from a data set – Excel is most useful to handle large volumes of data. When you are using such large volumes of data, it is likely that there are duplicates that you do not need. Luckily, this is easily dealt with by using the ‘Remove Duplicates’ command in the ‘Data Tools’ group under the ‘Data’ tab. To highlight unique or duplicate values you could also use the ‘Conditional Formatting’ command in the ‘Style’ group in the ‘Home’ tab.
5. Validate data to create drop-downs – Create a drop-down menu of selections to use in particular cells when you have a large list of data to handle, especially if it is a shared spreadsheet.
Highlight the cell and go to ‘Data Validation’ under the ‘Data’ tab. Select ‘List’ under ‘Allow:’ and type out your list, separated by commas in the ‘Source:’ field. Alternatively, you could click the button next to the source field and select the data series (you can hide that data later, if needed). Data validation is a good way to restrict the data entered and you can even create the exact error message you would like displayed.
6. Import data into Excel – The boon Excel grants us is to be able to use data from multiple data sources. Instead of using the copy+paste option of complex data sets, you can bring in the data by using the ‘Get External Options’ under the ‘Data’ tab and choosing the right options to be able to create drop-down lists and Pivot Charts or Tables as required.
7. Vague searches – You may want to find something in your large quantity of Excel data and the Ctrl+F option certainly helps with it. However, what if you remember only some of its letters or numbers? In such cases, Excel gives you wildcard characters to help you complete your search query.
- Question Mark – The “ ? ” can replace a single character. For example “B?nd” could mean Bind or Bond, but not Bound.
- Asterik – The “ * ” can take the place of any number of characters. For example “B*nd” could mean Bend, but could also mean Bound.
- Tilda – The “ ~ “ indicates that the character will be treated as a normal character and not a wildcard character. For example “B~?nd” can only mean “B~?nd”.
Bonus tip: You can never know all of the Excel shortcuts, so here are some you can add to your existing list.
Ctrl+; - Inserts today's date.
Ctrl+Shift+: - Inserts the current time.
Ctrl+Shift+# - Changes the format of a date.
Ctrl+5 - Applies a strikethrough to the text in a cell.
Ctrl+0— Hides the current column.
Ctrl+9 - Hides the current row.
Ctrl+F6 - Switches between open workbooks (that is, open Excel files in different windows).
Ctrl+` - That's the accent mark, up by the 1 key. This combo toggles the view in the sheet to show all the formulas. Ctrl+PageUp or PageDown allows a quick shift between the sheets in the currently open workbook.
F2 - Start editing the current selected cell (much faster than double-clicking).
Shift+F10 - Opens the right-click menu for the cell you're in.
Excel includes several lesser known features. Try your hand at Pivot tables, Data Query, Visual Basic Applications, Macros, etc. through Manipal ProLearn’s online training programs that offer certification courses in Excel.