by Celina Joyce D. Lazaro, JULY 2021
We learned about Microsoft Excel in school but back then we only know how to use the basic functions of this tool. Today, in the business world, MS Excel is used mostly to create spreadsheets. And now, it is at the forefront of analytics and data science. This tool is the world’s premier spreadsheet application that has gone from simple reporting to all sorts of analysis along with advanced visualization capabilities. Here are 6 basic Excel hacks to make the best use of the tool.
- Keyboard Shortcuts
Knowing the keyboard shortcuts will save you a lot of time. The following are some of the most used shortcut keys in MS Excel.
- |Ctrl + A|: will select all the data in the sheet
- |Ctrl + C|: will copy the selected data (or object)
- |Ctrl + V|: will paste the copied data (or object)
- |Ctrl + End|: will take you to the last cell of your data
- |Ctrl + Home|: will take you to the first cell of your data
- |Ctrl + Up Arrow|: will take you to the first cell of your active column
- |Ctrl + Down Arrow|: will take you to the last cell of your active column
- |Shift + Space|: will select row(s) of your active cell(s)
- |Ctrl + Space|: will select column(s) of your active cell(s)
- |Shift + Space|: will select row(s) of your active cell(s)v
- |Ctrl + minus sign|: will give you Delete options
For a complete list of keyboard shortcuts, visit: http://bit.ly/1QljQSp
2. Delete Duplicate Rows
To delete rows with duplicated data, follow these steps:
- Select the range of the data that you want de-duplicated (usually, |Ctrl + A| works).
- Click on the Data menu option from the menu ribbon.
- Click on the Remove Duplicates button.
- Choose whether your range has a header row.
- Hit OK.
3. Repeat Header for Printing
Trying to print in a multi-sheet with a header row on every page? To do this trick,
- Click on the Page Layout menu option on the ribbon.
- Click on the Print Titles button.
- Select the row(s) you want to repeat at the top in Rows to Repeat at Top box.
4. Ctrl+Shift to Select
There are much faster ways to select a dataset than using the mouse and dragging the cursor, especially in a spreadsheet that could contain hundreds of thousands of rows or columns. Click in the first cell you want to select and hold down Ctrl+Shift, then hit either the down arrow to get all the data in the column below, the up arrow to get all the data above, or left or right arrow to get everything in the row (to the left or right, of course). Combine the directions, and you can get a whole column as well as everything in the rows on the left or right. It’ll only select cells with data (even invisible data).
If you use Ctrl+Shift+End, the cursor will jump to the lowest right-hand cell with data, selecting everything in between, even blank cells. So if the cursor is in the upper-left cell (A1), that’s everything.
Ctrl+Shift+* (the asterisk) might be faster, as it will select the whole contiguous data set of a cell but will stop at blank cells.
5. Select all cells with one click
You can give yourself a finger cramp from tons of endless clicking and scrolling. You can also use this simple trick to select all cells with one single click. All it takes is clicking on that light gray triangle that appears in the top left corner of your spreadsheet. Click it once, and every single cell in the spreadsheet will be selected.
6. Quick Analysis
In the Home tab, you can see the Conditional Formatting Icon under the group “Styles”. This conditional formatting icon can give you a quick analysis of your data. Select the data you want to analyze and click on the conditional formatting icon. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. With conditional formatting, you can apply formatting to one or more cells based on the value of the cell. You can highlight interesting or unusual cell values, and visualize the data using formattings such as colors, icons, and data bars.
About the writer:
Celina Joyce D. Lazaro. Simply Celine. Graduated as a Computer Engineer in Bulacan State University and current position as Toll and Traffic Systems Associate. Bubbly but shy. Finds peace in quiet places. Also an optimist.