Microsoft Excel Tips & Tricks All In One you should know become microsoft excel expert 2020

Microsoft Excel Tips & Tricks All In One you should know become microsoft excel expert 2020

Tips and Tricks

Spread the love
86 / 100
Microsoft Excel is one of the most popular spreadsheet applications that helps you manage data, create visually persuasive charts, and thought-provoking graphs. Excel is supported by both Mac and PC platforms. Microsoft Excel can also be used to balance a checkbook, create an expense report, build formulas, and edit them.

Keyboard Shortcuts

1. ALT+= Inserts a SUM formula. 

2. CTRL+TAB Switches between open Excel windows.

3. CTRL+A – this has various scenarios:   a. If you are in regular data range and press CTRL+A all the data is selected. b. If you press CTRL+A a second time in the same range selects the entire spreadsheet. c. If you are in a table then pressing the CTRL+A key selects the data excluding the total row AND titles. d. If you press the CTRL+A key a second time it selects the data, titles, and total row e. It does not make any difference whether the spreadsheet contains data or not, if you are outside the data area, in a blank area with no directly adjacent cells containing data, CTRL+A selects the entire sheet. f. If you have one or more objects e.g. Charts, selected then pressing CTRL+A selects them all.

Also See

clickbank affiliate marketing Full course 2020

4. CTRL+1 Displays the Format Cells dialog box.

5. CTRL+SHIFT+” Copies the value from the cell above the active cell into the cell or the Formula Bar.

6. F4 Repeats an action, or if you’re editing a cell and the cursor is in between the cell references it will insert the $ signs for absolute references. Repeated pressing F4 will scroll through different levels of absolute references.

7. CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

8. CTRL+’ Copies a formula from the cell above the active cell into the cell or the Formula Bar.

9. CTRL+K Opens the Hyperlink dialog box.

10. CTRL+F Opens the Find dialog box.

11. CTRL+H Opens the Find & Replace dialog box.

12. CTRL+N Opens a new workbook.

13. CTRL+O Displays the Open dialog box to open or find a file. Note: In Excel 2013 it opens the File tab of the ribbon.

14. F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.

15. F7 Runs Spell Check on the entire worksheet if only one cell is selected, otherwise Spell Checks the selected range. You can also spell check multiple sheets by grouping them first.

16. CTRL+SHIFT+F3 Inserts named ranges for an entire table automatically based on the column or row headings (your choice).

17. CTRL+P Opens Print dialog box.

18. CTRL+S Saves workbook.

19. CTRL+C Copy

20. CTRL+X Cut

21. CTRL+V Paste

22. END key then Up or Down, or Left or Right Arrows OR the CTRL+Up Arrow/Down Arrow etc. Move to end of a range of cells (column or row). Your selected cell will stop at any empty cell in the range, or if cells are empty it will stop at the next populated cell in the column or row.

23. CTRL+HOME Quickly move to home. If you have frozen panes your cursor will stop at the intersection of the frozen panes.

24. CTRL+Page Up or CTRL+Page Down Scroll between worksheets 

25. CTRL+` View formulas instead of values (note the ` shares the tilde ~ key)

26. CTRL+D copies the cell above. Select a range or row and then CTRL+D to copy the row.

Tips & Tricks

27. Transpose Data – Copy data > Paste Special > Transpose

28. Increase Numbers by Set Amount – Enter the figure you want to increase numbers by, say 10%, you’d enter 1.1. Copy the cell containing 1.1 > highlight the cells containing the numbers you want to increase > Paste Special > Multiply. Bonus tip: convert negative values to positive by multiplying by -1 and vice versa.

29. AutoFill a Series or Formulas – Double Click on the + symbol on the bottom right of a cell that is adjacent to the range you want to fill. 

Microsoft Excel

30. Force a carriage return in a cell instead of wrapping the text – ALT+ENTER while editing the cell.

31. Use Format Painter more than once – Double Click the Format Painter and use it as many times as you like. When you’re done press ESC. Only applies in Excel 2007 and higher.

32. Format Sheet Tab Colours – Right-Click mouse on Sheet Tab > Tab Colour.

Microsoft Excel color

33. Combine Text from Multiple Cells – Enter your formula with the ampersand ‘&’ between the cell references e.g. =A1&A2&A3 will add the text in cell A1, A2 and A3 together. Note: if you want to add a space between the text from each cell enter your formula like this:  =A1&” “&A2&” “&A3 Where the “ “ is adding a space.

34. Delete blank cells in a row or column – Highlight the column or row containing cells you want to delete. Press CTRL+G to open the Go To Dialog Box > Special > Blanks.  Delete cells, rows or columns.

35. Fill blank cells in a row or column – Highlight the column or row containing cells you want to fill. Press CTRL+G to open the Go To Dialog Box > Special > Blanks.  Enter the text or formula you want to insert > press CTRL+ENTER to enter the text/formula in every blank cell.

36. Copy & Paste visible cells only – In a filtered list of data copy the list > Paste Special > Skip Blanks. Or if your list isn’t filtered use Go To Special to select visible cells only: CTRL+G > Special > Visible Cells Only > Paste. Or shortcut key ALT+;

Microsoft Excel Insert custom cell formats

38. Apply different formats within one cell – could be different fonts, font colours, styles etc. Select the cell you want to format > F2 to edit the cell > highlight the text you want to change > For Excel 2007+ use the formatting tools on the Home tab of the ribbon or for Excel 2003 use the formatting icons on the toolbar.

Move, insert and copy columns, rows and cells using the Mouse + SHIFT or CTRL.

39. Move column, row or cells: Select the range of cells, column(s) or row(s)  > hover your mouse over the edge of your selected range of cells (or columns or rows) >when the mouse

pointer changes to a 4 pointed arrow left click the mouse and hold down while you drag your cells to a new location.

40. Move and insert column, row or cells: As above except also hold down the SHIFT key while hovering your mouse over the edge of the selected area. Then drag the cells (while holding down the SHIFT key) and insert then in a new location.

41. Copy and paste a column, row or cells: As above except hold down the CTRL key while hovering your mouse over the edge of the selected area. Then drag the cells (while holding down the CTRL key) and release the mouse where you want to paste the data.

42. Copy and insert a column, row or cells: As above except hold down the CTRL+SHIFT keys while hovering your mouse over the edge of the selected area. Then drag the cells (while holding down the CTRL+SHIFT keys) and release the mouse where you want to insert your data.

microsoft excel

Note: these mouse pointers may appear different on your PC if you have a different operating system or have customised how your mouse appears. Not to worry, the shortcuts above will still work as described

43. Quickly enter links to a range of cells – Copy cells you want to link to > Paste Special > Paste Links.    

44. Convert formulas to values – Copy cells containing formulas > Paste Special > Paste Values.   46. Copy formulas only – Copy cell containing formula you want to copy > Paste Special > Paste Formulas.

45. Freeze rows and or columns at the top or left of your workbook so that headings stay in place while you scroll down the worksheet. Place your cursor at the intersection of the rows/columns you want fixed in place i.e. Frozen then For Excel 2003 > Window > Freeze Panes For Excel 2007+ > View tab of the ribbon > Freeze Panes

46. Synchronous Scrolling – want to compare two workbooks and have them both scroll at the same time?  With two workbooks open:  For Excel 2003 > Window > Compare Side by Side > Synchronous Scrolling For Excel 2007+ > View tab of the ribbon > View side by side > Synchronous Scrolling

Want to tamper-proof your workbook? 

48. Hide worksheet tabs > Windows Button > Excel Options > Advanced > Display Options > uncheck ‘Show sheet tabs’.

49. Hide row and column headers > Windows Button > Excel Options > Advanced > Display Options for this workbook > uncheck ‘Show row and column headers’ or View tab of the ribbon > uncheck ‘Headings’ in the Show/Hide group.

50. Hide the formula bar > View tab of the ribbon > uncheck ‘Formula Bar’ in the Show/Hide group.

51. Add non-contiguous print areas (Excel 2007+ only) – Set first print area, then select second print area and on the Page Layout tab of the ribbon select ‘Add to print area’.  Each print range will print on a separate page. 

52. Pick from a list of existing values – in the cell under your data hold down ALT + Down Arrow – Excel will give you a list of values to choose from. Use the arrow keys to select the one you want and press ENTER to insert it. 

53. Print titles on each page automatically – On the Page Layout tab of the ribbon select Print Titles. This will open the Page Setup dialog box. Enter your rows and or columns you want repeated in the boxes highlighted below by clicking in the box and then clicking on the row or column header on your worksheet.

microsoft excel Print titles on each page automatically

54. Absolute References – Understanding Absolute References is essential to working with formulas in Excel. Remember: use the F4 tip in the keyboard shortcuts when working with absolute references.

55. Quickly SUM a range of cells – select entire table (or it could be just a row or column of values) plus the blank cells you want your SUM formula in, then press the ATL+= keys.

microsoft excel tutorial

56. Generate a unique list of values from a range – Select the range > Data > Filter > Advanced Filter > Unique Records Only.  Note: select if you want to copy it to a new location or replace the existing data by selecting ‘Copy to another location’ and then inserting the ‘Copy to’ cell reference. 

microsoft excel tutorial

57. Use the AutoCalculate Menu in the bottom right of your Excel window to get a quick sum, average, or count. Right click on the area to alternate view Min, Max and more. Select the range of cells you want to sum/average/count. Hold down CTRL to select non-contiguous ranges.

58. You don’t have to start your formulas with = – If you’re a fan of the number keypad and arrow keys then it’s sometimes inconvenient to move your hand across to the = symbol every time you want to enter a formula. If you start a formula with + or even a – for negative values, Excel will put the = sign in for you when you press ENTER. After pressing ENTER your formula will look like this =+A1+A2 which is perfectly fine, or if your want to subtract the value in A1 it will look like this =-A1+A2.

59. Use Text to Columns to separate a column of data containing first names and last names – Let’s say you have First Names and Last Names separated by a space in column A. Select the cells > Data > Text to Columns > Delimited > Next > Choose Delimiter (space in this example) > Next > Select data format and Destination > Finish 

microsoft excel tutorial

60. Center Across Selection – Instead of Merging Cells, which puts limits on inserting and deleted columns and rows among other things, simply format the cells alignment to ‘Center across selection’. CTRL+1 to open Format Cells dialog box > Alignment tab > Horizontal Text Alignment set to ‘Center across selection’.

61. Find the number of days between two dates – Enter your dates in this formula inside double quotes =”20/6/2011″-“28/10/2006”  Result = 1696

Note: It’s best to always enter the dates in cells (without the double quotes) and then use a formula to calculate the days e.g. =B1-A1 

microsoft excel tutorial

62. Move quickly to the end of a range of cells – select a cell in the range > move your mouse to the edge of the cell until your mouse pointer changes to a 4 headed arrow > double click. You can do this on any edge of the cell to move in the direction of your choice.

64. Fill Handle Cool Tricks – Most of us know that you can left-click the mouse and drag the fill handle to fill a series (the fill handle is when your mouse pointer changes to a + symbol when hovered over the bottom right of the cell range – see image), but have you tried rightclicking the mouse while you drag the fill handle? There are myriad choices when you do this, so have a go and experiment.

microsoft excel tutorial

Download microsoft excel Android App Free

Please Share This Post in Your Social Media

Leave a Reply

Your email address will not be published. Required fields are marked *

© All rights reserved © 2019 TnewsPro.Com
Translate »