The ultimate guide to mastering Excel: Tricks, formulas, shortcuts and more

0
3

If you want to become an expert in the program, do not miss the collection of the best Excel tricks, formulas and shortcuts to use this program as a professional and gain time and efficiency.

Today, no job escapes the importance of managing spreadsheets with ease. Therefore, today we want to show you the best tips, tools and formulas in Excel, which will allow you to save time, productivity, ease and organization. It should be noted that whether you are self-employed or salaried, Excel management is practically compulsory, with multiple uses and applications around accounting, task planning or project management.

To learn how to use Excel Internet is a precious and inexhaustible source of resources: we recommend you from specialized YouTube channels such as Excel Made Easy, Excel Learn, Excel Functions or Specialists In Excel, as well as blogs focused on tricks and formulas to take advantage of Excel as Excel and VBA, the Microsoft Excel Learning Center, Excel Economy or Total Excel. There are also numerous MOOCs and free online courses around the Microsoft program on platforms such as Coursera, EdX or Udemy.

In case you didn’t believe it, Excel is much more than spreadsheets: its alternative uses range from budgeting to planning your goals, making inventories, making visual graphs and prototypes, delegating tasks, preparing invoices or calculating VAT. If you can even calculate the savings needed for your retirement! There is practically nothing you can’t do with this tool. And for sample, a button.

When it comes to using the Microsoft program at a professional level, if you are still a little fish in the subject you can take advantage of the many free templates that exist. If you want to have a cable, you can access the following functions, tricks and Excel shortcuts that are essential to maximize the potential of this program.

The function keys you should know in Excel

Excel Total experts collect the most important function keys in Excel:

  • F1: Show Excel help
  • F2: Enter edit mode for active cell
  • F3: If there is a defined name, it shows the Paste Name dialog box.
  • F4: Repeat the last action. In addition, writing a formula allows you to switch between relative, absolute and mixed references.
  • F5: Displays the Go to dialog box.
  • F6: Move between the panels of a divided book.
  • F7: Displays the Spelling dialog box.
  • F8: Activate the Expand selection mode that allows you to increase the selected range using the arrow keys.
  • F9: Calculate the result of the formulas of all the sheets of the open books.
  • F10: Activate the menu bar.
  • F11: Creates a chart sheet with the selected cell range.
  • F12: Displays the Save As dialog box.
READ  Why Is Online Education Important?

Most important keyboard shortcuts in Excel

  • Ctrl + A: Displays the Open dialog box.
  • Ctrl + B: Displays the Search dialog box.
  • Ctrl + C: Copy the selected cells.
  • Ctrl + D: Copy a formula to the right over the selected range.
  • Ctrl + E: Select all the cells in the current sheet.
  • Ctrl + G: Save the workbook.
  • Ctrl + I: Displays the Go to dialog box.
  • Ctrl + J: Copy a formula down over the selected range.
  • Ctrl + K: Applies italic formatting to selected text.
  • Ctrl + L: Displays the Replace dialog box.
  • Ctrl + N: Applies bold text to selected text.
  • Ctrl + P: Displays the Print dialog box.
  • Ctrl + R: Close the workbook.
  • Ctrl + S: Underlines the selected text.
  • Ctrl + T: Displays the Create Table dialog box.
  • Ctrl + U: New workbook.
  • Ctrl + V: Paste the contents of the clipboard.
  • Ctrl + X: Cut the selected cells.
  • Ctrl + Y: Redo the last undone action.
  • Ctrl + Z: Undo the last action.
  • Ctrl + 1: Displays the Format Cells dialog box.
  • Ctrl + 2: Apply bold formatting to selected text.
  • Ctrl + 3: Applies italic formatting to selected text.
  • Ctrl + 4: Underline the selected text.
  • Ctrl + 5: Apply the strikethrough effect to the text.
  • Ctrl + 6: Hide or show the objects on the sheet.
  • Ctrl + 8: Shows the outline symbols on the sheet.
  • Ctrl + 9: Hides the selected rows.
  • Ctrl + 0: Hides the selected columns.

Shortcuts with Ctrl and function keys

  • Ctrl + F1: Hide or show the Ribbon.
  • Ctrl + F2: Displays the Print dialog box.
  • Ctrl + F3: Displays the Name Manager.
  • Ctrl + F4: Close the current book window.
  • Ctrl + F5: Restores the current book window size.
  • Ctrl + F6: Move to the next open book.
  • Ctrl + F7: Move the book window when the window is not maximized.
  • Ctrl + F8: Change the size of the book window when the window is not maximized.
  • Ctrl + F9: Minimizes the current book window.
  • Ctrl + F10: Maximize the window of a minimized book.
  • Ctrl + F11: Insert a Microsoft Excel 4.0 Macros sheet
  • Ctrl + F12: Displays the Open dialog box.

Shortcuts with Shift and function keys

  • Shift + F2: Add or edit a cell comment.
  • Shift + F3: Displays the Insert Function dialog box. When editing a formula, it displays the Function Arguments dialog box.
  • Shift + F4: Execute the “Search next” command according to the search terms indicated previously.
  • Shift + F5: Displays the Search dialog box.
  • Shift + F6: Changes the focus between the sheet, the ribbon, the status bar and the task pane (if present).
  • Shift + F7: Displays the Reference task pane.
  • Shift + F8: Add another range of cells to the selection.
  • Shift + F9: Calculate the formulas of the current sheet.
  • Shift + F10: Displays the right click menu for selection.
  • Shift + F11: Insert a new sheet.
  • Shift + F12: Displays the Save As dialog box.
READ  How to Write the Perfect Sat Essay

Shortcuts with Alt and function keys

  • Alt + F1: Insert a graph in the current sheet.
  • Alt + F2: Displays the Save As dialog box.
  • Alt + F4: Close Excel.
  • Alt + F8: Opens the Macro dialog box.
  • Alt + F10: Displays the “Selection and visibility” panel for shapes.
  • Alt + F11: Open the Visual Basic for Applications Editor.

Shortcuts with Ctrl + Shift

  • Ctrl + Shift + F3: Displays the Create names dialog box from the selection.
  • Ctrl + Shift + F6: Move to the previous open book.
  • Ctrl + Shift + F10: Activates the menu bar.
  • Ctrl + Shift + F12: Displays the Print dialog box.
  • Ctrl + Shift + F: Displays the Source tab of the Format Cells dialog box.
  • Ctrl + Shift + L: Enable or disable filters in a range.
  • Ctrl + Shift + O: Select cells with comments.
  • Ctrl + Shift + U: Expand the formula bar.
  • Ctrl + Shift + Enter: Enter a formula as a matrix formula.
  • Ctrl + Shift + Start: Extends the selection to the beginning of the sheet.
  • Ctrl + Shift + End: Extends the selection to the last cell used in the sheet.
  • Ctrl + Shift + Page Down: Add the following sheet to the sheet selection.
  • Ctrl + Shift + Page Up: Add the previous sheet to the sheet selection.
  • Ctrl + Shift + Direction key: Extends the selection to the last non-empty cell in the same direction as the key pressed.
  • Ctrl + Shift + Space: Select the current cell range or the entire sheet.
  • Ctrl + Shift + (: Shows hidden rows within the selected range.
  • Ctrl + Shift +): Shows hidden columns within the selected range.
  • Ctrl + Shift + !: Applies the Number format with two decimals.
  • Ctrl + Shift + #: Applies the Date format in the form dd-mmm-yy.
  • Ctrl + Shift + $: Applies the Currency format with two decimals.
  • Ctrl + Shift +%: Apply the format Percent without decimals.
  • Ctrl + Shift + /: Applies the Scientific notation format.
  • Ctrl + Shift +: Applies the Time format.
  • Ctrl + Shift + &: Applies a border to the cell.
  • Ctrl + Shift + -: Remove the edges of the cell.
  • Ctrl + Shift + ”: Copy the contents of the upper cell.

LEAVE A REPLY

Please enter your comment!
Please enter your name here