Excel: How to use all the program's features with just one click

Contents: 

• Microsoft Excel. An indispensable tool for processing large amounts of data.
• The main groups of hotkeys. Let's look at examples.
• How to use hotkeys. Life hacks
• Useful resources for learning Excel from the editors of Skillbox.by

Microsoft Excel. A tool you can't do without when processing large amounts of data

Microsoft Excel contains about 500 hidden functions, and that's not counting the standard tools that are familiar to most users. But learning all the functionality is not necessary.

A spreadsheet processor is the correct name for this type of software. It is most often used by accountants, economists, financiers, marketers, warehouse and logistics workers. They use it to analyze advertising campaign results, generate payroll, maintain accounting and tax records, create graphs for presentations and business projects, and create databases of employees, clients, and products. It's a versatile tool, and different specialists use different features. Hotkeys significantly speed up and simplify working with spreadsheets. They eliminate the need to constantly switch to the mouse when formatting text, selecting, copying, or cutting data, or inserting a new row or column. Hotkeys make it easy to find functions that are deeply hidden in tabs and don't clutter the already overloaded toolbar with extra buttons.

Main groups of hotkeys. Let's look at examples

Excel uses standard Windows hotkeys, as well as those common to all Microsoft software. For example, keys for text formatting, editing, saving, undoing recent changes, and redoing them.

But Excel has hundreds of specific tools that can be found and used from the keyboard in a couple of keystrokes. Therefore, it is more convenient to divide hotkeys into several groups: basic function keys, text formatting, access to functions and formulas, selection and table navigation, navigation keys, rows and columns. Let's consider each group separately.

Main function keys

These are standard functions for editing and managing files that work in office applications and the operating system interface. Among the most popular function keyboard shortcuts that make working in Excel easier are:

• Ctrl + S.A standard combination for saving a file, which allows you to quickly save the changes you made. This option is especially relevant when working with large data arrays, when under high loads, crashes and program crashes are possible during operation.
• Ctrl + N. This keyboard shortcut is used to create a new workbook with tables — this is what a file is called in Excel. One workbook can contain many separate sheets with tables.
• Ctrl + C. This is a standard function that works in many applications, including word processors, photo editors, and Windows Explorer. In Excel, this keyboard shortcut is used to copy selected text or cells. If you copy an array of cells, they will be pasted in the same form in the new location without disturbing the table structure. If you copy the copied cells into a Word document, the embedded Excel table will be pasted with formatting and added formulas preserved, and not just the data from it.
• Ctrl + X.Like the previous combination, it works in Explorer and other office applications. It is used to cut the selected text or an array of cells.
• Ctrl + V. Hot keys for pasting data copied or cut using the combinations Ctrl + C, Ctrl + X, or through the menu by right-clicking.
• Ctrl + Z. A standard keyboard shortcut that works not only in Excel, but also in other software. Allows you to quickly undo the last action. For example, if you accidentally deleted the entered data. Pressing it again will undo the action preceding the last one, and so on. You can undo actions without limitation until the last manual or automatic save.
• Ctrl + Y. This is the antagonist of the Ctrl+Z combination. Allows you to redo the last action that was undone. If no actions have been undone before, then pressing the combination will repeat the last entered character.
• Ctrl + P. The combination opens the print settings page or immediately prints the document if such a function is set in the printer settings.

Text Formatting

In Excel, you can do more than just change fonts and underline text. In a spreadsheet, you can format cells for time and date, and customize how fractions are displayed. And hotkeys make this much more convenient—you don't have to take your eyes off the data you're entering. This not only speeds up your work, but also reduces the likelihood of typing errors.

Formatting shortcuts work both ways—they apply or remove a selected formatting from text. Popular keyboard shortcuts include:

• Ctrl + B. This shortcut applies the bold option to the selected text. If you press this shortcut while entering data, subsequent text will be bold.
• Ctrl + I. This shortcut applies the italic option to the text. Keyboard shortcuts can be used for selected text or while typing, without switching attention to the mouse and toolbar.
• Ctrl + U.Used for the "underlined text" option. The combination works on the same principle as the two previous ones.
• Ctrl + K.Opens a menu for setting up a hyperlink. Standard Excel functions allow you to add a link to any page on the Internet, link an email address or a specific cell in the document, including other sheets in the current workbook;
• Ctrl + 1. Opens the "Format Cells" menu. Allows you to apply the required format to the selected cell: general, number only, currency, time and date, percentages, fractions, text, etc. It is convenient to use when generating forms, reports and filling databases.

You can also apply a format directly to a cell using the combination Ctrl + Shift + "function":

# — date format;

@ — time;

% — percentage; 

! — numeric form; 

~ — general format, 

$ — currency.

Access to functions and formulas

This is a group of hotkeys with which you can quickly insert the required formula or use a function for a cell. Most hotkeys work exclusively in Excel and do not work in other office applications.

The most frequently used keyboard shortcuts:

• F2 — an alternative to double-clicking the left mouse button on a cell. A key for switching to cell editing mode. If you press this key, you can not only enter text in the selected cell, but also edit it, and move between words and numbers. During normal data entry, you cannot edit the text, but pressing the forward or back arrow will move to the next or previous cell.
• = (equal) or + (plus) or– (minus).If you click one of these symbols in an empty cell, the function entry is activated. Allows you to set up automatic summation and subtraction for selected cells.
• Shift + F3.This combination opens a menu for selecting a function. Excel has dozens of different functions, from standard arithmetic operations to root extraction, automatic discount deduction, and conversion of decimal data to hexadecimal or binary.
• Alt + =.Automatically sums data. By default, AutoSum is activated for the cells above. However, you can manually select a range of values ​​by selecting the desired cells using the Shift + arrow key combination.
• Ctrl + ; (semicolon) and Ctrl + : (colon). These combinations allow you to insert the current date and time into a cell. The appropriate format is also automatically assigned to the cell.
• Ctrl + E.Activates the "flash" function (edit: automatic data filling). With its help, the application automatically fills a column or row with data based on a specified pattern. This can be an arrangement of dates or row numbers. The option only works if a large enough number of cells in the table are filled for the application to determine the pattern.
• Alt + F1. Using this combination, you can quickly create a chart. Simply select the desired cells containing the information you want to plot the graph and press the hotkeys. After creating a chart, some settings for its appearance remain available.
• Ctrl + Shift + “ (double quotes, on the Cyrillic keyboard layout - the letter "Э"). This combination allows you to copy data from the cell above and paste it into the active cell without using the mouse.
• Alt, A, P, L, N. Call the Power Query editor, which only works in the desktop version for Windows. The tool allows you to automate calculations and data processing.
• Alt + F11. Call the VBA editor for recording macros. In Excel, you can independently program certain actions using the built-in Visual Studio language Basic. The possibilities are virtually limitless - you can create action scenarios for specific situations and automate complex business processes. This function is only available in the desktop version.

Selection and navigation through the table

When processing data in Excel, you often need to select an array of cells or quickly move to the beginning or end of a row or column. It is more convenient to do this using keyboard shortcuts, especially if you have large tables with hundreds or thousands of rows and columns. Some combinations may also work in other applications. For example, in Word.

Examples of hotkeys:

• Ctrl + A. This combination selects the entire workspace: a table or a separate data array - the action depends on which cell is selected when you click.
• Ctrl + ←, Ctrl + →, Ctrl + ↓, Ctrl + ↑. These combinations are used to quickly move around the table. These allow you to move to the beginning or end of a row or column without having to use the mouse wheel to scroll through the workbook.
• Shift + ←, Shift + ↑, Shift + →, Shift + ↓.A single press of this combination selects one cell in the chosen direction. To quickly select a larger area, you need to press and hold the buttons.
• Shift + Ctrl + ←, Shift + Ctrl + ↑, Shift + Ctrl + →, Shift + Ctrl + ↓.These combinations are usually used when working with large arrays of data. Allows you to select an entire row or column with a single click.

Navigation Keys

Allow you to quickly navigate between worksheets, open and save a workbook, and switch between tabs on the toolbar:

• Ctrl + H and Ctrl + F.The first combination opens the "find and replace" window, the second - "find". They are used when you need to quickly navigate to specific data. The tool is available on the taskbar, but you can quickly call it up using a hotkey combination.
• Keyboard shortcuts for navigating between tabs on the taskbar: Alt+F — File, Alt+H — Home, Alt+N — Insert, Alt+P — Page Layout, Alt+M — Formulas, Alt+A — Data, Alt+R — Review, Alt+W — View.
• Ctrl + Tab — Quickly switch between open workbooks in a file.

Lines and Columns

These keys allow you to quickly select, add, or delete columns and rows, as well as group data in cells and create pivot tables. These combinations are useful when entering large amounts of data and when editing forms, reports, and databases.

Popular combinations include:

• Ctrl + 0 or Ctrl + 9.These keys have similar functions. They allow you to hide selected columns or rows. To display hidden data again, you need to use similar combinations: Ctrl + Shift + 0 or Ctrl + Shift + 9.
• Ctrl + +. Sometimes, when entering data or editing existing reports, you may find that a row or column is missing. In this case, you can simply insert an additional row or column using the specified combination. If, on the contrary, you need to delete it, use the mirror combination: Ctrl + –. If you press the combination when no columns or rows are selected, a window with options for adding or deleting will open.

How to use hotkeys. Life hacks

Excel offers hundreds of shortcuts for quickly accessing various application functions. But you don't need to remember everything at once. The Skillbox.by editorial team recommends mastering the most common functions first. For example, copying and pasting, undoing an action, formatting text as bold and italic, saving, and switching to cell editing. It will be easy to remember hotkeys for selecting and navigating. And once these combinations are mastered to the point of automaticity, you can gradually make the task more challenging.

Associations will help when learning hotkeys. For example, some hotkey combinations are associated with a name. Underlining is activated with the command Ctrl + U, where U stands for "underline."

Another way to remember hotkeys faster is to learn them in groups. As a rule, the principle of calling functions for each group is similar. Some options have mirror combinations. For example, you can delete and add a row or column using Ctrl + + and Ctrl + -. The combinations for inserting time and inserting a date are distinguished by the Shift key. To hide a column, press Ctrl + 0, to show it again - Ctrl + Shift + 0.

Useful resources for learning Excel from the editors of Skillbox.by

Excel is an advanced tool for analyzing and processing large amounts of data. Mastering it is a good way to boost your career and increase your value to an employer. This is a must for small entrepreneurs who have to independently generate invoices, keep records of the balance, analyze sales, fill out statements and generate reports.

And so that you can get to know Excel better and explore its capabilities, the Skillbox.by editorial team has prepared a list of useful resources for you.

References:

• John Walkenbach "Excel 2019. The User's Bible";
• Alexey Gladkiy, Alexander Chirtik "Excel. Tricks and Effects";
• John Foreman "A Lot of Numbers";
• Greg Harvey "Excel 2019 for Dummies";
• Dmitry Zharov "Financial Modeling in Excel";
• Konrad Karlberg "Business Analysis Using Excel";
• Ratmir Vazinsky "Statistical Calculations in Excel".

Internet Resources:

• Excel Campus—On the blog, you can learn the basics of working with Excel, learn about the program's capabilities, tools, and features;
• Excel Help & Training (microsoft.com) — the official Microsoft page;
• exceljet.net — here you can find videos, useful articles, tutorials, and guides on working with Excel;
• www.howtoexcel.org — a blog dedicated to working in Excel;
• Tutorials, videos, useful tips for Excel, Outlook, and Google Sheets — A blog for the Excel and Outlook add-ons store. Here you can find lots of life hacks for your work;
• finances-analysis.ru is a blog by a financier and programmer where you can find examples and life hacks for working with Excel.