Top 10 Excel tips
Use Scenario Manager to generate summary outputs of a spreadsheet
Scenario Manager (under “What-if Analysis”) enables users to generate high-level, summary outputs of a spreadsheet – without the need to replicate the entire workbook. It will present multiple scenarios of a spreadsheet in a succinct, high-level summary worksheet.
Input Values Starting with
This is very important for the regular Excel users. When any input value starts with zero, Excel by default deletes the ZERO. So rather than reset the Format Cells, this problem is easily solved by adding a single quote mark ahead of the first zero.
Freeze Row and Column Headings
This is the simple but useful trick to becoming an Excel expert. Freeze the row and column headings so they are always viewable when you scroll around. To do so, LOOK Place the cursor in the top-left cell where the actual data starts and go to VIEW menu and simply select Freeze Panes and Freeze Panes. Now the heading is viewable where ever you scroll.
Learn your way around
Let’s start with the simple stuff. Launch Excel, then select Blank Workbook from the home screen and you’ll see a gridded area with columns labelled A, B, C and so on along the top, and rows numbered from 1, 2, 3 and onwards down the left-hand side. Each of the empty squares in the grid is known as a cell, and each cell is identified by the letter and the number where the column and the row intersect. So, for example, the cell in the top-left corner is cell A1. At the bottom of the worksheet, you’ll see a tab that says Sheet 1. Excel workbooks can be made up of multiple worksheets – click the + icon to the right of the tab to add another worksheet and click the tabs to switch between sheets.
Freeze Panes
Suppose you have a huge data titled nearly 50 subjects stored in an Excel sheet. In order not to lose track, freezing panes is the greatest way to keep the names insight as you scroll across the sheet. It can be applied to the worksheet in 3 different ways:
- Freeze panes
- Freeze top row
- Freeze 1st column. You can find all these 3 options in the View tab, and then by choosing Freeze option in the tools ribbon.
Adding frequent actions to quick access toolbar
Looking around any version of Excel you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. Instead of switching between the different tabs of the ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar. Microsoft offers several ways to make this happen but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar”. One of my personal favorites in my QAT is the Sort & Filter function as I usually work with a lot of data. Before: After: Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar...” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.
Excel Shortcut Keys
Just like every software has keyboard shortcuts, excel has its own too. There are many of them but below are the most relevant and widely used. Enjoy!
- Ctrl + ; this shortcut helps you put the current date
- Ctrl + Shift + : this keyboard shortcut will put the time
- Ctrl + Shift + # if you want to change the format of the date, this is your shortcut key
- Ctrl + 5 will put a strike-through on a text in a cell
- Ctrl + 0 is used to hide the desired column; mostly the current one
- Ctrl + 9 is used to hide the desired row; mostly the current one you’re working on
- Ctrl + F6 will help you switch open windows
- Ctrl + ` for toggling the view in the sheet and show the formulas
- Ctrl + PageUp or PageDown quick-shift between the sheets in the currently open workbook
- F2 to edit the selected but mostly current one cell
- Shift + F10 opens the right-click menu for the cell you’re in
Quickly Move and Copy Data in Cells
If you want to move one column of data in a spreadsheet then the easy and fast way is to select it and move the pointer to the border after it turns to a crossed arrow icon then drag to move the column freely. But if you want to copy the data, .then press the Ctrl button before dragging to move the new column will copy all the selected data.
Freeze Headers for Scrolling
The breaking point while working on a spreadsheet with a massive data set, is scrolling in any direction and trying to find that specific row or column you want. Users tired of this situation found a simple excel trick to deal with it. The trick is freezing a header row or column so that when you scroll, it doesn’t move. And below is how you do it:
- Go to the View tab and look for Freeze Panes
- You will find options like:
- If you want to do both select Freeze Panes
- If you changed your mind, select Unfreeze Pane – to reverse the freezing
IF
The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell "if" something else in your spreadsheet is true or false. For example, =IF(D2="Gryffindor","10","0") would award 10 points to cell D2 if that cell contained the word "Gryffindor." There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it. We'll go back to Sprung's example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10's next to each Gryffindor student's name, we'll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.
- The formula: IF(logical_test, value_if_true, value_if_false)
Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor." Make sure your Logical_Test value is in quotation marks. Value_if_True: If the value is true -- that is, if the student lives in Gryffindor -- this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number. Value_if_False: If the value is false -- and the student does not live in Gryffindor -- we want the cell to show "0," for 0 points.
- Formula in below example: =IF(D2="Gryffindor","10","0")