Interesting techniques and tips in excel. Adding New Buttons to the Quick Access Toolbar

Excel is not the friendliest program in the world, but it is very useful.

In contact with

Classmates

Regular user uses only 5% of its capabilities and has a poor idea of ​​what treasures are hidden by its bowels. Using the advice of an Excel guru, you can learn how to compare price lists, hide classified information from prying eyes and compose analytical reports in a couple of clicks.

1. Super secret leaf

Let's say you want to hide some of the sheets in Excel from other users working on the workbook. If you do this in the classic way- click right click on the sheet tab and click on "Hide" (picture 1), then the name hidden leaf will still be visible to another person.

To make it completely invisible, you need to proceed as follows: - Press ALT + F11. - On the left you will see an elongated window. - In the upper part of the window, select the number of the sheet you want to hide. - In the lower part, at the very end of the list, find Visible property and make it xlSheetVeryHidden.

Now no one but you will know about this sheet.

2. Prohibition of retroactive changes

Before us is a table with empty fields "Date" and "Quantity". Vasya's manager will indicate today how many carrots he sold per day. How do you prevent him from retroactively making changes to this table in the future?

Place the cursor on the cell with the date and select the "Data" item from the menu
.- Click on the "Data Check" button. A table will appear.
- In the drop-down list "Data type" select "Other".
- In the column "Formula" we write = A2 = TODAY ().
- Uncheck "Ignore empty cells".
- Press the "OK" button. Now, if a person wants to enter a different date, a warning message will appear.
- You can also prohibit changing the numbers in the "Quantity" column.

We put the cursor on the cell with the quantity and repeat the algorithm of actions.

3. Prohibition to enter duplicates

You want to enter a list of products in the price list so that they do not repeat. You can prohibit this repeat.

The example shows the formula for a column of 10 cells, but of course there can be any number of them.

Select cells A1: A10, which will be subject to the ban.
- In the "Data" tab, click the "Data Check" button.
- In the "Parameters" tab from the "Data type" drop-down list, select the "Other" option.
- In the column "Formula" we drive in = COUNT IF ($ A $ 1: $ A $ 10; A1)<=1.
- In the same window, go to the "Error message" tab and there we enter the text that will appear when you try to enter duplicates. - Click "OK".

4. Selective summation

In front of you is a table from which you can see that different customers several times bought different goods from you for certain amounts.

You want to know how much a customer named ANTON bought Boston Crab Meat from you.

In cell G4, you enter the customer's name ANTON.
- In cell G5 - the name of the product Boston Crab Meat
.- You stand on cell G7, where the amount will be calculated, and write the formula for it (= SUM ((C3: C21 = G4) * (B3: B21 = G5) * D3: D21)).

At first, it scares you with its volume, but if you write gradually, then its meaning becomes clear.

First, enter (= SUM and open parentheses that contain three factors.
- The first multiplier (C3: C21 = G4) looks for references to ANTON in the specified list of clients.
- The second multiplier (B3: B21 = G5) does the same with Boston Crab Meat.
- The third factor D3: D21 is responsible for the cost column, after which we close the parentheses.

5. Summary table

You have a table where it is indicated which product, to which customer, for what amount a particular manager sold.

When it grows, it is very difficult to select individual data from it. For example, you want to understand how much carrots were sold for or which of the managers completed the most orders.

Pivot tables are available in Excel to solve these problems.

To create such a table, you need:

In the "Insert" tab, click the "Pivot Table" button.
- In the window that appears, click "OK".
- A window will appear in which you can create a new table using only the data you are interested in.

6. Sales receipt

If you stop being afraid of formulas, you can do it more gracefully.

Select cell C7.
- Enter = SUM (.
- Select the range B2: B5.
- We enter an asterisk, which in Excel is a multiplication sign.
- Select the range C2: C5 and close the bracket (picture 2).
- Instead of Enter when writing formulas in Excel, you need to enter Ctrl + Shift + Enter.

7. Price comparison

This example is for advanced Excel users. Let's say you have two prices and you want to compare their prices. On the 1st and 2nd pictures we have prices from May 4 and 11, 2010.

Some of the goods in them do not match - here's how to find out what kind of goods they are.

Create another sheet in the book and copy the lists of goods from both the first and second price lists into it.
- To get rid of duplicate products, select the entire list of products, including its name.
- In the menu, select "Data" - "Filter" - "Advanced filter".
- In the window that appears, note three things:
a) copy the result to another location;
b) put the result in the range - select the place where you want to write the result, in the example this is cell D4;
c) put a tick on "Unique records only".

Press the "OK" button and, starting from cell D4, we get a list without duplicates.
- We delete the original list of goods.
- Add columns for loading price values ​​for May 4 and 11 and a comparison column.
- Enter the formula = D5-C5 in the comparison column, which will calculate the difference.
- It remains to automatically load the values ​​from the prices into the columns "May 4" and "May 11". To do this, use the function: = VLOOKUP (desired_value; table; column_number; interval_view)

.- "Lookup_value" is the line that we will look for in the price table. The easiest way to search for products is by their name.
- "Table" is an array of data in which we will search for the value we need. It must refer to the table containing the price list from the 4th.
- "Column_number" is the ordinal number of the column in the range that we set to search for data. For the search, we have defined a two-column table. The price is contained in the second one.
- interval_view. If the table in which you are looking for a value is sorted in ascending or descending order, you must set the value TRUE, if not sorted, write FALSE.
- Stretch the formula down, remembering to anchor the ranges. To do this, place a dollar sign in front of the column letter and in front of the line number (this can be done by highlighting the desired range and pressing the F4 key).
- The final column reflects the difference in prices for those items that are in both prices.

If # N / A is displayed in the final column, it means that the specified product is only in one of the prices, and therefore, the difference cannot be calculated.

8. Investment appraisal

In Excel, you can calculate the net present value (NPV), that is, the sum of the discounted values ​​of the flow of payments to date.

In the example, the NPV is calculated based on one investment period and four income generation periods (line 3 “Cash flow”).

The formula in cell B6 calculates NPV using a financial function: = NPV ($ B $ 4; $ C $ 3: $ E $ 3) + B3.
- In the fifth line, the calculation of the discounted flow in each period is found using two different formulas.
- In cell C5, the result is obtained thanks to the formula = C3 / ((1 + $ B $ 4) ^ C2) (picture 2).
- In cell C6, the same result is obtained through the formula (= SUM (B3: E3 / ((1 + $ B $ 4) ^ B2: E2))).

9. Comparison of investment proposals

In Excel, you can compare which of the two investment offers is more profitable.

To do this, you need to write out in two columns the required volume of investments and the amount of their phased return, and also separately indicate the discount rate of investment in percent.

Using this data, you can calculate the net present value (NPV).

In a free cell, you need to enter the formula = npv (b3 / 12, A8: A12) + A7, where b3 is the discount rate, 12 is the number of months in a year, A8: A12 is a column with numbers for a phased return on investment, A7 is the required amount of investments.
- According to the exact same formula, the net present value of another investment project is calculated. - Now they can be compared: whoever has more NPV, that project is more profitable.

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

Select all cells with one click

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

Opening multiple files at the same time

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.

Navigating 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, there are 3 buttons in the Excel Quick Access Toolbar. You can change this amount and add the ones that you need exactly.

Go to the menu "File" ⇒ "Options" ⇒ "Quick Access Toolbar". Now you can select whatever buttons you want.

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 page of Excel, click on the familiar border icon and select "Other Borders".

Add empty rows or columns to a table

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

High-speed copying and movement 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 that you need. If you need to copy information, do the same, but holding down the Ctrl key.

Quickly remove blank cells

Empty cells are the bane of Excel. Sometimes they just appear out of nowhere. To get rid of them all in one go, select the column you want, go to the Data tab and click Filter. A downward arrow appears above each column. Clicking on it will take you to a menu that will help you get rid of empty fields.

Advanced Search

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 "?" and "*". The question mark is responsible for one unknown symbol, and the asterisk - for several. They are worth using if you are not sure what the search query looks like.

If you need to find a question mark or an asterisk and you do not want Excel to search for an unknown character instead, then put "~" in front of them.

Copying unique records

Unique records can be useful when you need to highlight non-repeating information in a table. For example, one person of each age. To do this, select the required 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.

Sample creation

If you are doing a survey that only males between 19 and 60 can participate in, you can easily create a similar sample using Excel. Go to the menu item "Data" ⇒ "Data check" and select the required range or other condition. By entering information that does not meet this condition, users will receive a message that the information is incorrect.

Fast navigation with Ctrl and arrow keys

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.

Transpose information from a column to a row

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

Copy the range of cells to be transposed. 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 select the desired action.

Combining text with "&"

If you need to combine text from several cells into one, you do not need to use complex formulas. It is enough to select the cell in which the text will be connected, press "=" and sequentially select the cells, putting "&" in front of each.

Changing the case of letters

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

Entering information with zeros at the beginning

If you enter the number 000356 in Excel, the program will automatically convert it to 356. If you want to leave zeros at the beginning, precede the number with an apostrophe "’ ".

Speeding up difficult words

If you often enter the same words, you will be delighted to know that Excel has AutoCorrect. It is very similar to AutoCorrect in smartphones, so you will immediately understand how to use it. It can be used to replace repetitive designs with abbreviations. For example, Ekaterina Petrova - EP.

More information

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

Rename a sheet with a double click

This is the easiest way to rename a sheet. Just double-click on it with the left mouse button and enter a new name.

How often do you use Excel? If yes, then you probably have your own secrets of working with this program. Share them in the comments.

Which allow you to optimize work in MS Excel. And today we want to bring to your attention a new portion of advice to accelerate action in this program. Nikolay Pavlov, the author of the "Planet Excel" project, will tell about them, changing people's perception of what can actually be done with this wonderful program and the entire Office suite. Nikolay is an IT trainer, developer and product expert for Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Here are his personally tested tricks for getting faster in Excel. ↓

Quickly add new data to a chart

If for your already built chart on the sheet there is new data that needs to be added, then you can simply select a range with new information, copy it (Ctrl + C) and then paste it directly into the chart (Ctrl + V).

This feature only appeared in the latest version of Excel 2013, but it's worth upgrading to the new version early. Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (Ivanov I.I.). To perform such a transformation, you just need to start writing the desired text in the adjacent column by hand. On the second or third line, Excel will try to predict our actions and perform further processing automatically. All you have to do is press the Enter key to confirm, and all names will be converted instantly.

Similarly, you can extract names from emails, glue names from fragments, etc.

Copy without breaking formats

You probably know about the "magic" autocomplete marker - a thin black cross in the lower right corner of a cell, by pulling on which you can copy the contents of a cell or a formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided by clicking on the smart tag, a special icon that appears in the lower right corner of the copied area, immediately after dragging with a black cross.

If you select the Fill Without Formatting option, Microsoft Excel will copy your formula without formatting and will not spoil the layout.

In the latest version of Excel 2013, it became possible to quickly display your geodata on an interactive map, for example, sales by city, etc. To do this, go to the Office Store on the Insert tab and install the Bing plugin from there Maps. This can be done by a direct link from the site by clicking the Add button. Once a module has been added, you can select it from the My Apps drop-down list on the Insert tab and place it on your worksheet. It remains to select your cells with data and click on the Show Locations button in the map module to see our data on it.

If desired, in the plugin settings, you can select the type of chart and colors to display.

If your book has more than 10 worksheets, then it becomes difficult to navigate them. Right-click on any of the sheet tabs scroll buttons in the lower left corner of the screen.

Ever tinker with the input values ​​in your Excel calculation to get the output you want? At such moments you feel like a hardened artilleryman, right? Just a couple of dozen iterations of "undershoot - overflights", and here it is, the long-awaited "hit"!

Microsoft Excel can do this adjustment for you, faster and more accurately. To do this, on the Insert tab, click the What If Analysis button and select Insert - What If Analysis - Goal Seek. In the window that appears, specify the cell where you want to select the desired value, the desired result and the input cell that should be changed. After clicking on "OK" Excel will execute up to 100 "shots" in order to find the required total with an accuracy of 0.001.

If this detailed review did not cover all the useful MS Excel tricks that you know about, share them in the comments!

Thank you for your interest in our site. The IT company has existed since 2006 and provides IT outsourcing services. Outsourcing is the transfer of necessary, but non-core work for the company to another organization. In our case, these are: creation, support and maintenance of sites, promotion of sites in search engines, support and administration of servers running Debian GNU / Linux.

Joomla Websites

In the current age of information, the site is de facto becoming at least the hallmark of the organization, and often one of the business tools. Already, sites are being created not only for organizations and individuals, but also for individual goods, services and even events. Today, the site is not only a source of advertising for a giant audience, but also a tool for sales and making new contacts. We create sites using CMS Joomla! This site management system is simple and intuitive. It is very widespread and, therefore, there is a lot of information about it on the Internet. Finding a Joomla specialist is also easy. And you don't have to go far! Our company IT specialist is engaged in the maintenance and support of sites on Joomla! We will carry out all the technical work, take care of all the correspondence with the hoster and the domain registrar, fill the site and update the information on it. And while Joomla is easy to manage, it's intuitive. But will you yourself regularly perform the necessary work on the site? How long will they take from you? If you want to concentrate on your business, then entrust the support of your site to us. We will do our best to make the site live and benefit its owner.
If you are a commercial organization that advertises or sells its goods and services on the Internet, then you just need to promote your site in search engines. After all, in order to sell something you have to, at least, to see it, to know about it. And we will help you with this, we will promote your Joomla site in the search engines. Depending on the competition and the budget allocated for the promotion, your site will rank well in the search results. The site will increase your profits!

Debian servers

Sooner or later, striving for openness and transparency of their business, many companies are faced with the need to ensure the license purity of the software used. However, the costs of licensing fees are not always acceptable, especially for small and medium-sized businesses. The way out of this difficult situation is the decision to switch to Open Source technologies. One of the areas of Open Source is the Linux operating system (Linux). The employees of our company specialize in Debian Linux (Debian Linux). It is the oldest and most stable Linux distribution. We offer you services for the implementation of Debian Linux in your enterprise, configuration, maintenance and support of servers.

Information and advertising