Everyone has used Microsoft excel once in a while, be it for making university projects or workplace reports, even some job profiles require the sheer use of MS Excel. Undoubtedly, it’s a comprehensive way to create, edit and share spreadsheets offering many useful features to simplify our work. Following are some helpful tricks and keyboard shortcuts that make working on MS excel simply Awesome! for all, be it a beginner or master in excel. Just don’t miss it.
To keep secure excel spreadsheets from being edited, deleted or moved erroneously or maliciously, there’s an option of protecting it with the password. To set the password, go to ‘File’ > ‘Info’ > ‘Protect Workbook’. Users have multiple protection options like mark the file read-only, add a password, control what type of changes people can make to the file, prevent unwanted changes such as deleting rows or columns or add a digital signature.
Keyboard Shortcuts To Access Menu Bar
It’s not easy for everyone to coordinate with mouse and keyboard at the same time. In such a case, the trick of accessing menu bar through keyboard shortcuts is awesome. Press Alter key and the initial letter of the particular function such as ‘F’ for file, ‘H’ for home, ‘N’ for insert and so on. Further to reach out to more options within functions, follow the same technique of using initial letter. The letter that denotes the functions can be seen on the screen.
For e.g., if you want to set the margins, use the shortcut Alt > P > M.
Open Multiple Files on One-Click
It’s very time consuming to open excel files singly. Instead, simply hold the Ctrl key and make use of a mouse to select the files to open and hit ‘Enter’. All the selected files will open side by side. To quickly switch between the multiple opened files, use the Keyboard shortcut Alt + Tab. For ease, set the index finger and Thumb of your left hand on Tab and Alter key respectively.
Quick Data Transpose
While creating tabular formats in excel, sometimes it requires to transpose the rows and columns for better representation of data. In such a case, it’s laborious to draw it all over again. Use the function of Transpose to interchange the rows and columns. Select the cells, go to ‘Home’ > ‘Paste’ > ‘Transpose’.
Remove Duplicate Entries
The chances of repeating the entries is always there, notably when you’re working on large data files. Do not waste the valuable time in doing the legwork, let the excel to do it for you:
- Select data
- Go to ‘Data’ tab.
- Click on ‘Remove duplicates’.
- And ‘Ok’.
Freeze Top Rows and Columns
If it becomes annoying, to scroll back to the topmost row or leftmost column to check the Column/row headings each time for new making new entries, Excel allows you to freeze them.
Go to ‘View’ > ‘Freeze Panes > Freeze top row/ Freeze the first column.
Save as Another File Type
Change the file format from .xls to text, PDF, or others available. Go to ‘File’ > ‘Export’ > ‘Change File Type’. Contents of one file can also be exported to another excel file of older version say 2003.
Add Comments for Quick Notes
Comments can be added to each cell to remember any specific detail or information regarding it, which of course can’t be added to the cell value. To add comments, right click on that particular cell and click ‘Insert Comment’.
Some Lesser Known Yet Immensely Useful Keyboard Shortcuts
- Insert Current date : Ctrl + ;
- Insert Current Time : Ctrl + :
- Write values starting with zero : Press ‘ then write ‘0’
- Add multiple rows : Select & Ctrl + ‘+’
- Delete multiple columns : Select & Ctrl + ’-’
- Move quickly across the sheet : Ctrl + ‘Relevant Arrow Key’
- Insert Hyperlink : Ctrl + ‘K’
- Copy Formula : Ctrl + ‘
- Close the file : Ctrl + ‘W’
- Select complete row : Shift + Spacebar Key
- Select complete row : Ctrl + Spacebar Key
- Select only cells containing comments : Ctrl + Shift + ‘O’
- Hide Rows : Ctrl + ‘9’
- Hide Columns : Ctrl + ‘0’
- Unhide Rows : Ctrl + Shift + ‘(’
- Unhide Columns : Ctrl + Shift + ‘)’
- General Format : Ctrl + Shift + ‘~’
- Time Format : Ctrl + Shift + ‘@’
- Currency Format : Ctrl + Shift + ‘$’
- Percent Format : Ctrl + Shift + ‘%’
- Date Format : Ctrl + Shift + ‘#’
- Comma Format : Ctrl + Shift + ‘!’
For sure, above given tricks and keyboard shortcuts enhance user efficiency while working on excel spreadsheets. These tricks and shortcuts are useful to everyone and save a lot of time to fix simple things in a quick way.