When you know everything just excel. Secrets of Microsoft Excel

Facebook

In recent years, new “smart” technologies have appeared: smart phones, smart homes, refrigerators, phone cases, TVs, etc. Spreadsheets are no exception. Smart tables in Excel have been introduced since version 2010.

Concept of smart tables

The Excel workbook itself is a huge table. However, this table requires constant adjustment: drawing formulas, sorting, design, if this table needs to be presented in any document or presentation.

Microsoft, the creator of Excel, proposed calling smart tables tables, and what was previously called tables - ranges. However, in our country these ideas did not take root, so the tables remained tables, and the innovation began to be called smart tables.

When implementing smart tables, the perception of a spreadsheet document improves, data processing becomes easier, and it is brought into a specific system.

Create a smart table in Excel

Let's assume that we already have some data entered into Excel. In this case, select them, go to the “Main” ribbon menu and look through the groups of commands, find “Styles”, and click on the “Format as table” button in it.

A menu will open in which you will be asked to select a table style - select any one you like.

Next, a dialog box will appear in which you will be asked to specify the range of cells included in this table, and if the table contains headings (“header”), then you need to check the box next to the inscription “Table with headings”.

Then you need to confirm the completed actions by clicking on the “OK” button or pressing Enter.

As a result, the table will change in accordance with the selected style and will include filtering for each column.

Thus, without much effort, you can quite easily create a smart table in Excel.

Dropdown lists

One of the secrets to increasing the speed of entering data into a spreadsheet is using drop-down lists.

First, we create a list of data that should be in a drop-down list on another sheet of the application or to the side of the data to be printed.

Select the cell in which this list will be located, go to the “Data” tab, select the “Working with Data” group and click “Data Validation”.

In the window that appears, click on the arrow next to “Data Type” and select “List”.

Then we move along this window down to the “Source” field. Place the cursor there or click on the icon to the right of this field and select the range of cells that will be included in the drop-down list.

We confirm the changes made. As a result, we will receive your data.

In order to avoid error messages when entering incorrect data, you need to click on “Verify data” again, click on the “Error message” tab and uncheck “Display an error message”. This will allow you to enter any necessary data into a cell with a drop-down list, and not just those that are present in the drop-down list.

In order to be able to constantly add new data to this list, you need to use smart Excel tables for drop-down lists.

First, we create a smart table with a header, which contains only the header and a list of the components of the drop-down list, then we create this list as described above, without selecting the header. After this, we confirm the changes made. This will allow you to constantly update the list with new values.

The presented method is one of the secrets of working effectively with smart tables in Excel.

Changing the smart table style

If you follow the instructions, you will get a smart table of a certain style, but in the menu it is presented symbolically and you may not always like how it looks on the screen. Therefore, if necessary, the style can be changed.

How to make a smart table in Excel with a new style? To do this, you do not need to select the entire table; just place the cursor in the form of a black rectangle on any cell of the smart table.

After this, move to the “Designer” tab, “Table Styles” group. If the presented styles are not enough, click on “Advanced options” and see the entire palette of styles.

Select the style you like, which will be applied to the created smart table.

By the way, if you don’t like any style, you can click on the “Create table style” link below.

Style options

Smart tables in Excel 2010 and older, as well as in Excel 2007, can be customized depending on your preferences and needs.

To do this, move the cursor to any table cell. Go to the "Designer" tab, "Table Style Options" group, and there check/uncheck the options that interest us: "Filter button", "Alternating columns or rows", "Header or total row", "First or last column" .

Using Formulas

There is no need to copy or paste formulas in Excel smart tables. Creating a new column or new row leads to the fact that the formulas in the newly created table elements are pulled into the required cells themselves.

If you need to create a new column in which you need to enter a formula, in a cell of this column we enter a formula that is slightly different from the standard one for this type of spreadsheet. She looks like [@ [(Column name)]]arithmetic operations.

Here (Column name) is the specific name of the column in which the calculation is carried out, for example “Amount, rub.”, arithmetic operations are algebraic expressions accepted in Excel formulas and, if necessary, numbers, @ - indicates that the data will be taken from the same line.

These formulas can be entered as usual in Excel, by clicking on the appropriate fields and only entering the necessary functions; in this case, all the auxiliary icons necessary for the formula will be added automatically.

When you press Enter, this column will be recalculated in all cells of the table.

If it is necessary to calculate at the end of the totals table, on the “Designer” tab in the “Table Style Options” group, check the “Totals Row” option, which will allow you to calculate the totals for those columns where the formulas were entered.

If you are interested in this information, have a good command of foreign languages and will look for additional sources of information abroad, remember that in Russia and abroad there are different separators for list elements. For them it is a comma, but for us it is a semicolon, which must be taken into account in formulas.

Autofiltering

As mentioned above, when creating smart tables in Excel, an autofilter appears in each column of their header, with which you can set what needs to be displayed or, conversely, not displayed at a given specific point in time. For example, you can use keys such as "more than", "less than", "contains", "does not contain", etc. This feature can be used when working with large amounts of data.

For example, there is a table with customers, and another column indicates the months of purchases. We are only interested in February. In order not to manually search for February buyers, click on the arrow in the “Month” column and put a tick there next to “February”, unchecking the other checkboxes. This way, only customers who made purchases in February will be shown. The rest will not disappear anywhere; if you click the arrow in the “Month” header again and tick all the months, then all the customers will appear on the screen again.

Set the table name

After a smart table is created in Excel, the application assigns a name to it in accordance with the order in which it appears (if it is the first table, it will be named “Table1”, etc.).

In order to find out the name of the table, place the cursor in any of its cells, go to the “Designer” tab, “Properties” group and there we find the “Table Name” parameter, which can be edited, for which you need to place the cursor on “Table1”, select and enter a new name, then press Enter.

This name can be used in formulas, built-in functions, drop-down lists.

The table name can be used when applying GPR. For example, we want to find who our 1229th customer was. To do this, enter 1229 in some free cell, for example K1, and enter the formula =VLOOKUP(K1;Table1 (or its modified name);1) in cell L1. Here 1 is the number of the column in which the search will be performed. As a result, the buyer’s full name under No. 1229 will appear in cell L1 (if, of course, it was in the first column).

Addressing in smart tables

Smart Tables in Excel allow you to use field names instead of a range of data cells. So, to calculate the sum in column C from the 1st to the 21st cell, you can enter the formula commonly used in Excel = SUM(C1:C21). Suppose that in column C we have entered information on the amount of purchase made by each specific buyer, and this column is called “Amount, rub.”, and the table has been renamed “Buyers”. Thus, the following expression can be entered as a formula: = SUM(Customers[Amount, rub.]).

In addition, you can use special addressing:

"=Customers[#This Row]" will refer to the current row.
"=Customers[#Data]" will refer to the data, ignoring the header lines.
"=Customers[#Headers]" will refer to the table column headers located in the first row of the table.
“=Buyers[#Totals]” will refer to the total row (if it exists in the smart table).
"=Customers[#All]" will refer to the entire table, including all the above formulas.

Additional features of smart tables

Scrolling the columns of a table, given its rather large size, does not require fixing areas, since the names of the table columns are moved to the column headings of a specific sheet of a specific book.

Excel 2010 introduced the ability to create slicers when working with smart tables. They are an autofilter for a single column, presented as a graphic element. Go to the “Designer” tab, group of commands “Tools”, click “Insert Slice”, in the dialog box select the names of the columns (or one column) from which the slice will be formed. If you need to filter the table, select the required category or categories by holding down the Ctrl button.

How to Delete an Excel Smart Table

Sometimes a situation may arise when you need to save data in a regular spreadsheet format, removing sorting, automatic formulas, addressing and other gadgets of smart tables from it, that is, leaving the table only as a database.

In Excel, you can turn a smart table into a regular one. To do this, place the cursor in any cell of the smart table, go to the “Designer” tab, “Service” command group, click on “Convert to range”.

As a result, a dialog box will appear; confirm the action by clicking on the consent button or pressing the Enter key. After this, the table will be saved as usual, preserving the last applied style and data.

Finally

Thus, smart tables are the same tables that you can create yourself. But their use allows you to effectively use preparation time by reducing manual operations, beautifully design tables, and use graphic elements. If you suddenly don’t like the created smart table, you can always convert it to a regular one.

Grouping data

When you are preparing a product catalog with prices, it would be a good idea to worry about its ease of use. A large number of positions on one sheet forces you to use a search, but what if the user is just making a selection and has no idea about the name? In Internet catalogs, the problem is solved by creating product groups. So why not do the same in an Excel workbook?

Organizing a group is quite simple. Select several lines and click the button Group on the tab Data(see Fig. 1).

Figure 1 – Group button

Then specify the grouping type - line by line(see Fig. 2).

Figure 2 – Selecting a grouping type

As a result, we get... not what we need. The product lines were combined into a group indicated below them (see Fig. 3). In directories, the title usually comes first, and then the content.

Figure 3 – Grouping rows “down”

This is not a program error at all. Apparently, the developers considered that the grouping of rows is mainly done by the preparers of financial statements, where the final result is displayed at the end of the block.

To group rows “up” you need to change one setting. On the tab Data click on the small arrow in the lower right corner of the section Structure(see Fig. 4).

Figure 4 – Button responsible for displaying the structure settings window

In the settings window that opens, uncheck the item Totals in the rows below the data(see Fig. 5) and press the button OK.

Figure 5 – Structure settings window

All groups that you have created will automatically change to the “top” type. Of course, the set parameter will also affect the further behavior of the program. However, you will have to uncheck this box to everyone a new sheet and each new Excel workbook, because The developers did not provide for a “global” setting of the grouping type. Likewise, you cannot use different types of groups within the same page.

Once you have categorized your products, you can organize the categories into larger sections. There are up to nine grouping levels in total.

The inconvenience when using this function is that you have to press a button OK in the pop-up window, and it will not be possible to collect unrelated ranges in one go.

Figure 6 – Multi-level directory structure in Excel

Now you can open and close parts of the catalog by clicking on the pluses and minuses in the left column (see Figure 6). To expand the entire level, click on one of the numbers at the top.

To move rows to a higher level of the hierarchy, use the button Ungroup tabs Data. You can completely get rid of grouping using the menu item Delete structure(see Fig. 7). Be careful, it is impossible to cancel the action!

Figure 7 – Ungrouping rows

Freeze areas of the sheet

Quite often when working with Excel tables, it becomes necessary to freeze some areas of the sheet. There may be, for example, row/column headings, a company logo or other information.

If you freeze the first row or the first column, then everything is very simple. Open the tab View and in the drop down menu To fix areas select items accordingly Freeze top row or Freeze first column(see Fig. 8). However, it will not be possible to “freeze” both a row and a column at the same time.

Figure 8 – Freeze a row or column

To unpin, select the item in the same menu Unlock areas(the item replaces the line To fix areas, if “freezing” is applied on the page).

But pinning several rows or an area of rows and columns is not so transparent. You select three lines, click on the item To fix areas, and... Excel “freezes” only two. Why is that? An even worse scenario is possible, when areas are fixed in an unpredictable way (for example, you select two lines, and the program sets the boundaries after the fifteenth). But let’s not attribute this to the developers’ oversight, because the only correct way to use this function looks different.

You need to click on the cell below the rows that you want to freeze, and, accordingly, to the right of the columns to be docked, and only then select the item To fix areas. Example: in Figure 9 the cell is highlighted B 4. This means that three rows and the first column will be fixed, which will remain in place when scrolling the sheet both horizontally and vertically.

Figure 9 – Freeze an area of rows and columns

You can apply a background fill to frozen areas to indicate to the user that these cells behave differently.

Rotate the sheet (replacing rows with columns and vice versa)

Imagine this situation: you worked for several hours on typing a table in Excel and suddenly realized that you had designed the structure incorrectly - the column headings should have been written by rows or rows by columns (it doesn’t matter). Do I have to re-type everything manually? Never! Excel provides a function that allows you to “rotate” a sheet 90 degrees, thus moving the contents of the rows into columns.

Figure 10 – Source table

So, we have some table that needs to be “rotated” (see Fig. 10).

Select cells with data. It is the cells that are selected, not the rows and columns, otherwise nothing will work.
Copy them to the clipboard using a keyboard shortcut or in any other way.
Move to an empty sheet or free space of the current sheet. Important Note: You cannot paste over current data!
Inserting data using a key combination and in the insert options menu select the option Transpose(see Fig. 11). Alternatively, you can use the menu Insert from tab home(see Fig. 12).

Figure 11 – Insert with transposition

Figure 12 – Transpose from the main menu

That's all, the table has been rotated (see Fig. 13). In this case, the formatting is preserved, and the formulas are changed in accordance with the new position of the cells - no routine work is required.

Figure 13 – Result after rotation

Showing formulas

Sometimes a situation arises when you cannot find the desired formula among a large number of cells, or you simply do not know what and where to look. In this case, you will need the ability to display on a sheet not the result of calculations, but the original formulas.

Click the button Show formulas on the tab Formulas(see Figure 14) to change the presentation of the data on the worksheet (see Figure 15).

Figure 14 – “Show formulas” button

Figure 15 - Now the formulas are visible on the sheet, not the calculation results

If you have difficulty navigating the cell addresses displayed in the formula bar, click Influential cells from tab Formulas(see Fig. 14). The dependencies will be shown by arrows (see Fig. 16). To use this function, you must first highlight one cell.

Figure 16 – Cell dependencies are shown by arrows

Hides dependencies at the touch of a button Remove arrows.

Wrapping lines in cells

Quite often in Excel workbooks there are long inscriptions that do not fit into the width of the cell (see Fig. 17). You can, of course, expand the column, but this option is not always acceptable.

Figure 17 – Labels do not fit into cells

Select cells with long labels and click the button Wrap text on Home tab (see Fig. 18) to go to the multi-line display (see Fig. 19).

Figure 18 – “Wrap text” button

Figure 19 – Multi-line text display

Rotate text in a cell

Surely you have encountered a situation where text in cells needed to be placed not horizontally, but vertically. For example, to label a group of rows or narrow columns. Excel 2010 includes tools that let you rotate text in cells.

Depending on your preferences, you can go two ways:

First create the inscription, and then rotate it.
Adjust the rotation of the text in the cell, and then enter the text.

The options differ slightly, so we will consider only one of them. To begin with, I combined six lines into one using the button Combine and place in the center on Home tab (see Fig. 20) and entered a general inscription (see Fig. 21).

Figure 20 – Button for merging cells

Figure 21 – First we create a horizontal signature

Figure 22 – Text rotation button

You can further reduce the column width (see Figure 23). Ready!

Figure 23 – Vertical cell text

If you wish, you can set the text rotation angle manually. In the same list (see Fig. 22) select the item Cell alignment format and in the window that opens, set an arbitrary angle and alignment (see Fig. 24).

Figure 24 – Setting an arbitrary text rotation angle

Formatting cells by condition

Conditional formatting features have been around for a long time in Excel, but have been significantly improved by the 2010 version. You may not even have to understand the intricacies of creating rules, because... the developers have provided many preparations. Let's see how to use conditional formatting in Excel 2010.

The first thing to do is select the cells. Next, on Home tab click button Conditional Formatting and select one of the blanks (see Fig. 25). The result will be displayed on the sheet immediately, so you won’t have to go through the options for a long time.

Figure 25 – Selecting a conditional formatting template

Histograms look quite interesting and well reflect the essence of information about the price - the higher it is, the longer the segment.

Color scales and sets of icons can be used to indicate different states, such as transitions from critical to acceptable costs (see Figure 26).

Figure 26 – Color scale from red to green with intermediate yellow

You can combine histograms, scales, and icons in a single range of cells. For example, the histograms and icons in Figure 27 show acceptable and excessively poor device performance.

Figure 27 – A histogram and a set of icons reflect the performance of some conditional devices

To remove conditional formatting from cells, select them and select Conditional Formatting from the menu. Remove rules from selected cells(see Fig. 28).

Figure 28 – Removing conditional formatting rules

Excel 2010 uses presets to quickly access conditional formatting capabilities because... Setting up your own rules is far from obvious for most people. However, if the templates provided by the developers do not suit you, you can create your own rules for the design of cells according to various conditions. A full description of this functionality is beyond the scope of this article.

Using filters

Filters allow you to quickly find the information you need in a large table and present it in a compact form. For example, you can choose works by Gogol from a long list of books, and Intel processors from the price list of a computer store.

Like most operations, a filter requires selecting cells. However, you do not need to select the entire table with data; just mark the rows above the required data columns. This significantly increases the convenience of using filters.

Once the cells are selected, on the tab home click the button Sorting and Filter and select Filter(see Fig. 29).

Figure 29 – Creating filters

The cells will now transform into drop-down lists where you can set selection options. For example, we are looking for all mentions of Intel in the column Name of product. To do this, select the text filter Contains(see Fig. 30).

Figure 30 – Creating a text filter

Figure 31 – Create a filter by word

However, it is much faster to achieve the same effect by entering the word in the field Search context menu shown in Figure 30. Why then call an additional window? This is useful if you want to specify multiple selection conditions or select other filtering options ( does not contain, starts with..., ends with...).

For numeric data, other options are available (see Figure 32). For example, you can select the 10 largest or 7 smallest values (the number is customizable).

Figure 32 – Numeric filters

Excel filters provide quite rich capabilities comparable to selection with a SELECT query in database management systems (DBMS).

Displaying information curves

Information curves (infocurves) are an innovation in Excel 2010. This function allows you to display the dynamics of changes in numerical parameters directly in a cell, without resorting to building a chart. Changes in numbers will be immediately shown on the micrograph.

Figure 33 – Excel 2010 info curve

To create an info curve, click on one of the buttons in the block Infocurves on the tab Insert(see Figure 34), and then specify the range of cells to plot.

Figure 34 – Inserting an info curve

Like charts, information curves have many options for customization. A more detailed guide to using this functionality is described in the article.

Conclusion

The article discussed some useful features of Excel 2010 that speed up work, improve the appearance of tables or ease of use. It doesn't matter whether you create the file yourself or use someone else's - Excel 2010 has functions for all users.

With the release of Excel 2010, Microsoft has almost doubled the functionality of this program, adding many improvements and innovations, many of which are not immediately noticeable. Whether you're an experienced user or a beginner, there are many ways to make using Excel easier. We will talk about some of them today.

Select all cells with one click

All cells can be selected using the Ctrl + A key combination, which, by the way, works in all other programs. However, there is an easier way to select. By clicking on the button in the corner of the Excel sheet, you will select all cells with one click.

Opening multiple files at once

Instead of opening each Excel file individually, you can open them together. To do this, highlight the files you want to open and press Enter.

Navigate Excel Files

When you have multiple workbooks open in Excel, you can easily navigate between them using the Ctrl + Tab key combination. This feature is also available throughout the Windows system and can be used in many applications. For example, to switch tabs in the browser.

Adding new buttons to the Quick Access Toolbar

By default, Excel's Quick Access Toolbar contains 3 buttons. You can change this quantity and add those that you need.

Go to File ⇒ Options ⇒ Quick Access Toolbar. Now you can select any buttons you need.

Diagonal line in cells

Sometimes there are situations when you need to add a diagonal line to the table. For example, to separate date and time. To do this, on the main Excel page, click on the familiar borders icon and select “Other borders”.

Adding empty rows or columns to a table

Inserting a single row or column is quite simple. But what if you need to insert many more of them? Select the required number of rows or columns and click "Insert". After that, select the place where you want the cells to move, and you will get the required number of empty lines.

High-speed copying and moving of information

If you need to move any information (cell, row, column) in Excel, select it and move the mouse over the border to change the pointer. After that, move the information to the place you need. If you need to copy information, do the same, but with the Ctrl key held down.

Quickly remove empty cells

Blank cells are the bane of Excel. Sometimes they just appear out of nowhere. To get rid of them all at once, select the desired column, go to the “Data” tab and click “Filter”. A downward arrow will appear above each column. By clicking on it, you will be taken to a menu that will help you get rid of empty fields.

Advanced Search

By pressing Ctrl + F, we get to the search menu, with which you can search for any data in Excel. However, its functionality can be extended by using the "?" characters. And "*". A question mark represents one unknown character, and an asterisk represents several. They are worth using if you are not sure what the query you are looking for looks like.

If you need to find a question mark or asterisk and you don’t want Excel to look for an unknown character instead, then put “~” in front of them.

Copying unique entries

Unique entries can be useful if you need to highlight non-repeating information in a table. For example, one person of each age. To do this, select the desired column and click “Advanced” to the left of the “Filter” item. Select the source range (where to copy from) and the range where you want to place the result. Don't forget to check the box.

Creating a selection

If you are doing a survey that is limited to men aged 19 to 60, you can easily create a similar sample using Excel. Go to the menu item “Data” ⇒ “Data Validation” and select the required range or other condition. When entering information that does not meet this condition, users will receive a message that the information is incorrect.

Quick navigation using Ctrl and arrow

By pressing Ctrl + arrow, you can move to the extreme points of the sheet. For example, Ctrl + ⇓ will move the cursor to the bottom of the sheet.

Transposing information from a column to a row

Quite a useful feature that is not needed very often. But if you suddenly need it, you are unlikely to transpose one at a time. There is a special paste for transposing in Excel.

Copy the range of cells you want to transpose. After that, right-click on the desired location and select Paste Special.

How to hide information in Excel

I don’t know why this might be useful, but nevertheless there is such a function in Excel. Select the desired range of cells, click Format ⇒ Hide or Show, and choose the desired action.

Combining text with "&"

If you need to combine text from multiple cells into one, you don't have to use complex formulas. Just select the cell in which the text will be connected, press “=” and select the cells sequentially, placing the “&” symbol in front of each one.

Changing the case of letters

Using certain formulas, you can change the case of all text information in Excel. The "UPPERCASE" function makes all letters uppercase, and the "LOWER" function makes all letters lowercase. "PROPNACH" capitalizes only the first letter of each word.

Entering information with leading zeros

If you enter the number 000356 into Excel, the program will automatically convert it to 356. If you want to leave leading zeros, put an apostrophe “’” in front of the number.

Speed up typing difficult words

If you type the same words often, you'll be glad to know that Excel has AutoCorrect. It's very similar to autocorrect on smartphones, so you'll immediately understand how to use it. With its help, you can replace repeated structures with abbreviations. For example, Ekaterina Petrova is an EP.

More information

In the lower right corner you can monitor various information. However, few people know that by right-clicking there, you can remove unnecessary lines and add the necessary lines.