Microsoft Excel is without a doubt the best spreadsheet program on the market and the one most used by businesses, students and individuals. Excel allows you to store data, dates and numeric values. You can create formulas, charts and graphs to help you analyze your data better. Microsoft has built in many shortcuts to make it easier for those using Excel to perform basic functions. The more shortcuts you learn, the faster and easier it is to maximize your use of Microsoft Excel.
Below is a look at the top 30+ MS Excel hacks and tips that even your grandma will love. Whether you are a beginner or advanced Excel user, these tips will help.
Beginner Excel Tips
- Quick Navigation Tools
- Rename Spreadsheets
- Quick Sum
- Enter Number as a Text
- Select All
- Select a Large Portion of Data
- Insert Columns or Rows
- Fill with Same Information
- Copy Entire Worksheet
- Quick View of Formulas
Intermediate Excel Tips
- Open Multiple Files at the Same Time
- Freeze Panes
- Including Multiple Cells in Formula
- Include Permanent Number in Formula
- Autofill Technique
- Cut and Paste Simplified
- Instantly Format Multiple Cells
- Change Columns to Rows or Vice Versa
- Create a Chart Template
- Use Filter to See Specific Information
- Eliminate Duplicate Content
Advanced Excel Tips
- Shift From File to File
- Create a Table within a Spreadsheet
- Select Non-Joining Cells
- Combine Text in Multiple Cells into One Cell
- Break Down One Cell into Two
- Change Case Letters
- Restrict Input
- Remove Blank Cells
- Have Excel Repeat Number to You
- Customize Shortcut Menu
Beginner Excel Tips
You can easily navigate within your spreadsheet by using the Ctrl button and any of the arrow buttons (up, down, left or right).
Rename any sheet within a spreadsheet by double clicking on the tab at the bottom and then typing in the new name.
Click the cell where you want the sum to go – Click Home – Sum button – Highlight the portions you want to add together – Hit Enter.
If you want Excel to view a number as text, all you need to do is put a single apostrophe (‘) before the number.
You can select every cell in your worksheet by clicking on the empty box at the right-hand top corner (at the intersection of Row 1 and Column A) of your spreadsheet.
Click in the first cell of the data – click Ctrl and Shift buttons – scroll down and click the last cell of data.
Highlight the number of rows/columns you want to add, right click and hit insert.
If you want to copy the exact same data to multiple adjoining cells, simply highlight the text to be copied, move the cursor to the bottom right-hand corner of the highlighted box to display a plus sign and click and drag it down as far as needed.
You can move an entire sheet within your spreadsheet to a new file. Simple right click on the Data Sheet Name tag at the bottom of the spreadsheet and click Copy. Then go to the file where you want to add the page and click Paste.
An easy way to check formulas is to view your document in Format View. Click the Formula tab and select Show Formulas.
Intermediate Excel Tips
Click File and Open. Hold down the Ctrl button and select every file you want to open. Then hit the Open button.
Freezing the panels makes it easier to see your headings as you are working within your spreadsheet. Click where you want to freeze the panes and then go to the View tab and click Freeze Panes.
Start in the cell where you want the value to be displayed – enter an “=” sign – enter the formula to use – hold the Ctrl button while you click all the value cells you want to include in the formula.
If you want to copy a formula, but you have a certain number within the formula that you don’t want changes during the process, place a $ sign in front of the number or the cell, such as $A$1.
Microsoft Excel is very advanced and it can automatically fill in missing information. For example, if you want to write a number series counting by twos, type in the first few numbers – highlight these numbers – move your cursor to the bottom right corner until a plus sign appears, click and drag it down as far as you want the series of data to continue.
Highlight the section to be moved. Point your cursor at the edge of the highlighted box until the cross arrow cursor appears, then click and drag the section where it needs to go.
Format a range of cells simultaneously by highlighting the section to be formatted – right clicking – selecting Format Cells – make the necessary changes – hit OK.
Highlight and copy the areas to be transposed – right click – Paste Special – Transpose.
Don’t waste all the hours you put into designing the perfect chart. Save it as a template. In Chart Tools, click Save Template.
If you only want to see specific information from your spreadsheet, use the Filter button on the Home tab. Use the drop-down box at the top of each column to select the type of information you want to see displayed.
Check and remove duplicate content by going to the Data tab – click Remove Duplicate Content – Select the rows to check – click OK.
Advanced Excel Tips
If you have more than one MS Excel file opened at the same time, you can quickly shift between these files by using the Ctrl and Tab button.
You can quickly add a table by highlighting the data to include and clicking on the Ctrl and T button simultaneously.
You just need to hold down the Ctrl button while clicking on every cell you want to select.
Click where you want the merged cells to go, click the = sign – click the first cell to add – click the & sign – add the second cell and so on.
Under the Data tab, click Text to Columns tab. Use the Text to Column wizard to determine your specific set of parameters.
Change the letters in a cell to all caps (=Upper(cell location)), all lowercase letters (=Lower(cell location)) or Proper (=Proper(cell location)).
This can be done by clicking Data – Data Validation – Settings. Use the drop down box to select the type of data type.
You can quickly remove all the blank cells in your document with the Filter function. On the Home tab select Sort & Filter, select filter, use the drop-down box at the top of the column and un-click the Select All option and then click Blank, this will highlight all the blank spaces, at which point you can hit the Delete button.
Excel will read each number you input by going to File – Options – Customize Ribbon – use the drop down box to select Commands Not in the Ribbons – select Speak Cells on Enter.
You can customize your shortcut menu by clicking File – Options – Quick Access Toolbar – Add the functions you want – Click Save.