Microsoft Excel is a spreadsheet used for collecting and managing big sets of quantitative data. The software provides numerous tools that allow to represent information visually through plotting graphs and organizing tables. In addition to formulae that can be applied to cells of the spreadsheets to speed up calculations, the menu ribbon at the top of the page also contains shortcuts to format data and the way information is presented.
To help you navigate through the bulk of the information in Excel, we have collected some of the neat time-saving tricks which will hopefully swing your vote in favour of using Excel more often.
How to Select All?
If case you need to select all the information in the spreadsheet, you have two options. You can either apply the Ctrl+A shortcut which selects all the information across all platforms, or you can simply click on the corner cell in the top left corner. Don’t worry about accidentally deleting the data by pressing a key on the keyboard as even if you accidentally click “Backspace”, a window will pop up asking whether you’d really like to delete all cells.
How To Open Several Excel Spreadsheets At Once?
This little trick will save you time double clicking each of the documents you need to open and then exiting and repeating the same process multiple times. Simply select several Excel spreadsheets using touchpad/mouse or by holding Ctrl and right-clicking on each of the documents. Then simply press Enter and all the spreadsheets will open simultaneously.
How To Add Multiple Rows Or Columns?
In case you need to insert multiple rows or columns in between existing cells, this trick is a time-saver but it needs to be executed correctly. Let’s say you need to insert three columns between the Column C and Column D. What you need to do, is to first press Ctrl and left-click on the cells “A”, “B”, “C”, after that right-click anywhere and select Insert from the drop down menu. Then insert the selected number of rows either to the right or to the left of the column selected.
How To Change The Case Of The Text?
If you need to apply upper or lower case to the text in some of the cells, there is a formula that spares you the unnecessary repeated actions. First, you need to select an empty cell and type in one of the options: =proper/=upper/=lower. Proper will capitalize each word in the cell and the upper/lower case will do as it says on the tin. Then open up the brackets and select the cells you need to change. Once you have a formula that looks something like =proper(A2), hit enter and the change should occur.
Create AutoCorrect Shortcuts!
Some of the typing shortcuts such as “ttyl” (talk to you later) have become engraved into the texting process, however, the same exact principle can be applied when inserting text into the spreadsheets. Instead of typing New Zealand, simply create a shortcut which will automatically replace NZ with the full text. To do that, go to File, select Options, AutoCorrect Options and click on “Replace text” with the correct text.
Add $ And % To Multiple Cells
In case you have a column of prices and percentages, instead of manually inserting the signs into each cell, simply select the needed cells and press either Ctrl+Shift+$ or Ctrl+Shift+% and the signs will automatically appear. It is worth mentioning that you can select the cells with your inputs and the sign will also appear in the cell with a formula you applied to the selected range.
Copy The Entire Worksheet
In case you find yourself using the same format spreadsheets on a regular basis you can apply this shortcut to copying the entire page. This will enable you to save the formatting, formulae and other features of the existing spreadsheet. To copy the worksheet, right click on the bar on the bottom of the page and select Move or Copy. Then a window will pop up asking where you’d like to move the spreadsheet. You can either duplicate it in a new spreadsheet or the existing one.
Shortcuts To Navigate Through The Spreadsheet
Making the most of shortcuts enables you to quickly navigate through the spreadsheet without having to scroll up and down too much. Ctrl+ ↓↓will take you to the last row while Ctrl+↑↑ will take you to the first row. Use Ctrl+→→ to go to the last column and similarly do the opposite to return to the first one. Pressing Ctrl+Home will bring you back to the Cell A1 and if you want to do the opposite, press Ctrl+End to get the last column of the last row used in the sheet.
Draw A Diagonal Line Through A Cell
In case you want to go the extra mile to make your spreadsheet look neat, you can insert diagonal lines to separate text within a cell. To do that, select a cell, right click the cell and go to Format CellsàBorder tabàDiagonal line option. Once you click OK, type the text and press Alt+Enter for it to appear at the top of the diagonal line. Repeat the same process for the bottom part of the cell.
More Little Tricks
To crate a column chart based on the data in the spreadsheet, select the range of the cells and click F11. In case you want your columns to become rows and the other way around, select and copy the range of cells, click where you want it to go and choose Transpose in Paste Special.
Additional Resources For Developing Excel Skills
In case you want your Excel skills to a new level, here are a few free resources to help you master navigating through the spreadsheets. To help you build the mastery from the ground up, sign up for the Coursera Everyday Excel Part 1 course. It is aimed at learners with no or little experience in Excel. In this course you will learn how to navigate, edit and format the spreadsheets as well as implement basic Excel functions.
The Technology for Teachers and Students YouTube playlist is a great option to go for in case you need quick answers to problems at hand. The videos range from four to twenty minutes in length and aim at explaining one feature at a time. Or in case you prefer podcasts, we got you covered too. Player.fm offers numerous talk shows with the sole purpose of explaining how Microsoft Excel works without using fancy words and professional jargon.
Although Microsoft Excel is not used as often as Microsoft Word or PowerPoint presentations, the spreadsheets do speed up the number crunching process once you get the gist of how to play the game. With a bit of perseverance and practice, soon enough you’ll be able to genuinely say that Microsoft Excel is amongst your strengths on the resume.
Photos: Shutterstock / Edited by: Martina Advaney
More articles on Digital Skills: