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

Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated ones (Ivanov I. I.). To do this, you just need to start writing the desired text in the adjacent column manually. 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. In a similar way, you can extract names from email, merge full names from fragments, and so on.

You most likely know about the magic autofill marker. This is a thin black cross in the lower right corner of a cell, by pulling it you can copy the contents of the 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. Immediately after pulling the black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area.

If you select the “Copy values ​​only” option (Fill Without Formatting), Excel will copy your formula without formatting and will not spoil the design.

In Excel, you can quickly display your geodata, such as sales by city, on an interactive map. To do this, you need to go to the “App Store” (Office Store) on the “Insert” tab and install the “Bing Maps” plugin from there. This can also be done from the site by clicking the Get It Now button.

After adding a module, you can select it from the My Apps drop-down list on the Insert tab and place it on your worksheet. All you have to do is select your data cells 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 the number of worksheets in a file exceeds 10, then it becomes difficult to navigate through them. Right-click on any of the sheet tab scroll buttons in the lower left corner of the screen. A table of contents will appear, and you can go to any desired sheet instantly.

If you've ever had to manually move cells from rows to columns, you'll appreciate the following trick:

1. Select a range.
2. Copy it (Ctrl + C) or by right-clicking and select “Copy”.
3. Right-click the cell where you want to paste the data and select one of the paste special options from the context menu - the Transpose icon. Older versions of Excel do not have this icon, but you can solve the problem by using Paste Special (Ctrl + Alt + V) and selecting the Transpose option.

If in any cell you are supposed to enter strictly defined values ​​from the allowed set (for example, only “yes” and “no” or only from a list of company departments, and so on), then this can be easily organized using a drop-down list.

1. Select the cell (or range of cells) that should contain such a restriction.
2. Click the “Data Validation” button on the “Data” tab (Data → Validation).
3. In the “Type” drop-down list, select the “List” option.
4. In the “Source” field, specify a range containing reference variants of elements that will subsequently appear as you enter.

If you select a range with data and on the “Home” tab click “Format as Table” (Home → Format as Table), then our list will be converted into a smart table that can do a lot of useful things:

1. Automatically expands when new rows or columns are added to it.
2. The entered formulas will be automatically copied to the entire column.
3. The header of such a table is automatically fixed when scrolling, and it includes filter buttons for selection and sorting.
4. On the “Design” tab that appears, you can add a total line with automatic calculation to such a table.

Sparklines are miniature diagrams drawn directly in cells that visually display the dynamics of our data. To create them, click the Line or Columns button in the Sparklines group on the Insert tab. In the window that opens, specify the range with the original numerical data and the cells where you want to display sparklines.

After clicking the “OK” button, Microsoft Excel will create them in the specified cells. On the “Design” tab that appears, you can further configure their color, type, enable the display of minimum and maximum values, and so on.

Imagine: you close the report you've been fiddling with for the last half of the day, and the "Save changes to file?" dialog box appears. suddenly for some reason you press “No”. The office is filled with your heart-rending scream, but it’s too late: the last few hours of work have gone down the drain.

In fact, there is a chance to improve the situation. If you have Excel 2010, then click on “File” → “Recent” (File → Recent) and find the “Recover Unsaved Workbooks” button in the lower right corner of the screen.

In Excel 2013, the path is slightly different: “File” → “Information” → “Version Control” → “Recover Unsaved Workbooks” (File - Properties - Recover Unsaved Workbooks).

In later versions of Excel, open File → Details → Manage Workbook.

A special folder will open from the depths of Microsoft Office, where temporary copies of all created or modified, but unsaved books are saved in such cases.

Sometimes when working in Excel, you need to compare two lists and quickly find the elements that are the same or different. Here is the fastest and most visual way to do this:

1. Select both columns to compare (hold down the Ctrl key).
2. Select on the Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
3. Select the Unique option from the drop-down list.

Have you ever tweaked the input values ​​in your Excel calculation to get the output you want? At such moments, you feel like a seasoned artilleryman: just a couple of dozen iterations of “undershooting - overshooting” - and here it is, the long-awaited hit!

Microsoft Excel can do this adjustment for you, faster and more accurately. To do this, click the “What If Analysis” button on the “Data” tab and select the “Parameter Selection” command (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 change. After clicking “OK,” Excel will perform up to 100 “shots” to find the total you require with an accuracy of 0.001.

A book by Excel guru John Walkenbach. A weighty Talmud that makes no sense to read from cover to cover - refer to it as a reference for any questions. That’s exactly what, for everyone (well, almost) - a very wide coverage of topics. There's even a section on macro basics to give you a basic understanding of them. Formulas and graphs are discussed in detail.

It should be noted that Walkenbach makes such books for each version. 2007, 2010 and 2013 are not so different from each other, and I still get by with the book based on the 2010 version.

In the Russian edition, the 2010 version is made on slightly poorer paper, but with higher quality screenshots.

I would like to note the great work of the Russian publishers of Walkenbach’s books - they localized all the examples and files.

This book is better executed than the User's Bible. Nice paper and illustrations.

Suitable if you already know the basics and want to delve deeper into pivot tables, formulas, and data analysis. The book contains examples and detailed descriptions of basic Excel functions, array formulas, complex and little-known formulas (for example, RAZNDAT, which is not in the function wizard and in the auto-completion list - it can only be entered manually if you know about it) and - what, perhaps, the most valuable thing is advanced formulas created from several functions (such as, for example, a formula that allows you to find the occurrence of some text in a cell -)

Although short basics of Excel are given here too - so if you are not a complete beginner, you can skip the first book in favor of this one.

Some examples of useful formulas from the book:

Formula to calculate the quarter number by date:

=ROUNDUP (MONTH(A1)/3,0)

Counting the number of unique values ​​in an interval (array formula):

(=SUM(1/COUNTIF(Interval,Interval)))

Search for the closest value in a range (we look for the number closest to “Target” from the “Data” array)

(=INDEX(Data,MATCH(SMALL(ABS(Target-Data),1),ABS(Target-Data),0)))

Effect of alternating lines in conditional formatting:

Create a condition for the formula:

=REMAT(ROW();2)=0

and paint the cells with the desired color.

There are hundreds of such examples in the book. My copy was covered in sticky notes, highlighting interesting and useful ideas.

The best Excel book for those involved in analysis, statistics, reporting, and working with large arrays.

The most complete and high-quality guide to creating macros in Excel. She came to the rescue in many situations, and was very helpful in creating complex projects in VBA (a la the automatic creation of analytical reports in Word based on management reporting in Excel).

Included with the book, the author offers files with all the examples discussed in it. There are a lot of them. This is invaluable material that allows you to quickly learn how to write macros in practice, combining the “poke method” with information from the book.

By the way, there are the same example files for the first two books in this review.

95% of the time I can find the answer to an Excel question in one of these three books by Walkenbach. His books are larger and more expensive than the countless “self-teaching books” on the market. But it’s better to invest in them once and have high-quality information at hand than to be fooled by the apparent simplicity of a tutorial that promises quick and fundamental mastery of the material :)

This book is for those who love the format of “life hacks” and ready-made solutions.

Excel, unlike the first book on the list, is not suitable for fundamental mastery, but it is highly likely to help you quickly solve your current work problem. The book contains 100 tips such as “How to make two charts the same size,” “Detecting extra spaces,” and “Removing minus signs from negative numbers.”

I note that the VBA language changes little and does not depend on versions. For a long time I used the book “Excel 2003. Professional programming in VBA” and only when the 2013 version was released I acquired a new edition. So in the case of this book, don’t let the 2010 in the title confuse you.

There are tons of useful articles and video tutorials there. By the way, the author of this site has his own book with ready-made techniques and solutions. And it is offered with example files. I recommend.

In order to understand how to use this program, you need to consider EXCEL formulas with examples.

If you place the mouse cursor on any cell and click on the “select function” item, the function wizard appears.

With its help, you can find the required formula as quickly as possible. To do this, you can enter its name and use the category.

Excel is very convenient and easy to use. All functions are divided into categories. If the category of the required function is known, then its selection is carried out according to it.

If the function is unknown to the user, he can set the category “full alphabetical list”.

For example, the task is given to find the SUMIFS function. To do this, you need to go to the category of mathematical functions and find the one you need there.

VLOOKUP function

Using the VLOOKUP function, you can extract the necessary information from tables. The essence of vertical scanning is to find the value in the leftmost column of a given range.

After which the total value is returned from the cell, which is located at the intersection of the selected row and column.

The calculation of the VLOOKUP can be traced using an example that provides a list of surnames. The task is to find the last name using the given number.

Using the VLOOKUP function

The formula shows that the first argument of the function is cell C1.

The second argument A1:B10 is the range in which the search is carried out.

The third argument is the ordinal number of the column from which the result should be returned.

Calculate a given last name using the VLOOKUP function

In addition, you can search for a surname even if some serial numbers are missing.

If you try to find a last name from a non-existent number, the formula will not give an error, but will give the correct result.

Searching for a last name with missing numbers

This phenomenon is explained by the fact that the VLOOKUP function has a fourth argument, with which you can set interval viewing.

It has only two meanings - “false” or “true”. If the argument is not specified, it is set to true by default.

Rounding numbers using functions

The program's functions allow you to accurately round any fractional number up or down.

And the resulting value can be used in calculations in other formulas.

Numbers are rounded using the ROUNDUP formula. To do this, you need to fill in the cell.

The first argument is 76.375 and the second is 0.

Round a number using a formula

In this case, the number was rounded up. To round the value down, select the ROUND DOWN function.

Rounding occurs to the nearest whole number. In our case, up to 77 or 76.

Excel helps simplify any calculations. Using a spreadsheet you can complete higher mathematics assignments.

The program is most actively used by designers, entrepreneurs, and students.

The whole truth about Microsoft Excel 2007 formulas

EXCEL formulas with examples - Instructions for use

28
Jul
2009

Excel 2007

ISBN: 978-5-699-24209-2
Format: PDF, Scanned pages
Year of manufacture: 2008
Igor Pashchenko
Genre: Educational literature
Publisher: EKSMO
Number of pages: 496
Description: IT literature - Office applications - Excel 2007
With this book, you will become familiar with the basic capabilities of Microsoft Excel 2007, designed for creating and managing spreadsheets. You will be able to quickly master all the necessary techniques, since all the necessary material is presented as briefly and clearly as possible, using clear step-by-step instructions. The book describes in a simple and accessible form how to customize the program for yourself, work with formulas and functions, build charts and graphs, perform data analysis and processing, and prepare tables for printing. You will be able to do this and much more after reading I. Pashchenko’s book Excel 2007, based on the author’s many years of experience in teaching computer courses. The book fully and accurately answers the questions that a novice user may have. Why is this? and How to do it?, paying particular attention to the differences between the new version of the program and its previous versions.

14
Jan
2015

Working in MS Office 2007: Word, Excel, PowerPoint (Novikovsky E.A.)

Author: Novikovsky E.A.
Year of manufacture: 2012
Genre: Computer literature
Publisher: AltSTU
Russian language
Number of pages: 230
Description: This manual is devoted to a description of the main programs of the Microsoft Office 2007 package - Microsoft Office Word 2007, Microsoft Office Excel 2007 and Microsoft Office PowerPoint 2007 - in order to obtain basic skills when working with them. It includes many images and notes to improve the perception and retention of information. At the end of each chapter there are control questions that allow...

27
Jun
2015

Excel - ready-made solutions (Nikolai Pavlov)

ISBN: 978-5-519-01837-1
Format: PDF, eBook (originally computer)
Author: Nikolay Pavlov
Year of manufacture: 2014
Genre: Computer literature
Publisher: Book on Demand
Russian language
Number of pages: 382
Description: This book is a complete collection of ready-made solutions to the most common problems and tasks that Microsoft Excel users encounter in their daily work. The book's material covers all main areas and topics: working with text and dates, formatting, calculations using formulas and functions, processing large amounts of data and creating analyzes based on them...

09
Aug
2015

Excel 2013. Complete guide (V. Serogodsky, A. Rogozin, D. Kozlov, A. Druzhinin, R. Prokdi)

ISBN: 978-5-94387-970-8
Format: PDF, OCR without errors
Authors: V. Serogodsky, A. Rogozin, D. Kozlov, A. Druzhinin, R. Prokdi
Year of manufacture: 2015
Genre: Computer literature
Publisher: Science and Technology
Series: Complete Guide
Russian language
Number of pages: 416
Description: With this book, you will learn how to competently use the professional capabilities of Excel 2013, you will be able to significantly increase the efficiency of your work and learn how to quickly solve a variety of problems. Particular attention is paid to such important issues as carrying out calculations (from solving algebraic equations to using...

16
Jul
2015

Excel 2013 User's Bible (John Walkenbach)

ISBN: 978-5-8459-1872-7
Format: DjVu, Scanned pages
Author: John Walkenbach
Translator: N. Voronina
Year of manufacture: 2015
Genre: Computer literature
Publisher: Dialectics
Series: Professional Library
Russian language
Number of pages: 928
Description: Master the latest version of Excel with the help of a great guide - the book "Excel 2013. User's Bible"! Whether you want to create charts, import and manipulate data, automate tasks with VBA macros, or work in the cloud while still working in Excel, John Walkenbach shows you how...

04
Oct
2013

A clear tutorial for Excel 2013 (Alexey Lebedev)

ISBN: 978-5-496-00786-3
Format: PDF, OCR without errors
Author: Alexey Lebedev
Year of manufacture: 2013
Genre: Computer literature
Publisher: Peter
Russian language
Number of pages: 128
Description: This book will teach you how to work in Microsoft Excel - one of the most popular and in-demand office programs. The book is written in simple, accessible language, equipped with practical examples and tasks for independent mastery of the material, and is intended for a wide range of users. The publication examines the interface of the latest version of Excel, covers issues of organizing Excel workbooks, formatting...

10
Aug
2014

Microsoft Office Excel 2010 (Viktor Dolzhenkov, Alexander Stuchenkov)

ISBN: 978-5-9775-0594-9
Format: PDF, eBook (originally computer)
Authors: Viktor Dolzhenkov, Alexander Stuchenkov
Year of manufacture: 2011
Genre: Computer literature
Publisher: BHV-Petersburg
Series: In the original
Russian language
Number of pages: 816/919
Description: The book is the most comprehensive guide to working with spreadsheets and contains comprehensive information on almost all functions of Microsoft Office Excel 2010. The changes and innovations made by Microsoft in the latest version are reflected. The full cycle of data processing, presentation and analysis is described. Consider...

15
May
2011

A clear tutorial for Excel 2010 (V. Volkov)

ISBN: ISBN 978-5-49807-771-0
Format: PDF, Scanned pages
Author: V. Volkov
Year of manufacture: 2010
Genre: Computer literature
Publisher: Peter
Russian language
Number of pages: 252
Description: Microsoft Excel is one of the most mysterious and interesting programs in the MS Office 2010 package. It is interesting for its numerous means of automating work, document preparation and rich computing capabilities. The mystery of it lies in the fact that most users use only a small fraction of what Excel can give them. This is all the more surprising since the range of capabilities of the program...

10
Aug
2014

Excel 2010 with examples (Alexey Vasiliev)

ISBN: 978-5-9775-0578-9
Format: PDF, eBook (originally computer)
Author: Alexey Vasiliev
Year of manufacture: 2010
Genre: Computer literature
Publisher: BHV-Petersburg
Series: Examples
Russian language
Number of pages: 432/628
Description: Specific examples show the capabilities of the popular office application Microsoft Office Excel 2010. The features of the version, a significantly changed graphical interface, resources (settings, hyperlinks, notes, printing and add-ins), formatting and application of styles, data processing methods, programming in VBA and other questions. ...

16
Jun
2014

Business analytics using Excel (Yana Gobareva, Olga Gorodetskaya, Anatoly Zolotaruk)

ISBN: 978-5-9558-0282-4, 978-5-16-006229-7
Format: DjVu, OCR without errors
Author: Yana Gobareva, Olga Gorodetskaya, Anatoly Zolotaruk
Year of manufacture: 2013

Publisher: University textbook, Infra-M
Russian language
Number of pages: 336
Description: The capabilities of the Microsoft Excel 2010 spreadsheet processor are described in detail. The technology for analyzing and solving financial and economic problems is described in detail, using numerous examples, figures, tables, charts and charts. Options for laboratory and test work and independent assignments for completion are given...

08
Apr
2015

Excel 2010 in examples (Karchevsky E.M., Filippov I.E.)

Format: PDF, eBook (originally computer)
Author: Karchevsky E.M., Filippov I.E.
Year of manufacture: 2012
Genre: Computer literature
Publisher: Kazan University
Russian language
Number of pages: 100
Description: Specific examples show the capabilities of the popular office application Microsoft Office Excel 2010. In the textbook you will find: First acquaintance; Elementary data sorting; Graphic representation of table data; Working with diagrams; Using pictures in diagrams; Working with lists; Sorting; Using filters; Working with formulas and functions; Logical expressions...

27
Jul
2014

Microsoft Excel 2010. User's Bible (+ CD-ROM) (John Walkenbach)

ISBN: 978-5-8459-1711-9
Format: PDF, OCR without errors
Author: John Walkenbach
Translator: N. Voronin
Year of manufacture: 2013
Genre: Computer literature
Publisher: Dialectics
Russian language
Number of pages: 912
Description: This book is intended for developers and spreadsheet users of both entry-level and intermediate levels, as well as advanced users who want to learn more about the capabilities of Excel in general and the nuances of working with the new version of Excel - Excel 2010 - in particular. Written by an Excel guru, this book will teach you how to use Excel 2010 to work with spreadsheets,...

08
Feb
2017

MS Excel in calculation problems (T.R. Kosovtseva, V.Yu. Petrov)

ISBN: 978-5-9558-0282-4
Format: PDF, eBook (originally computer)
Author: T.R. Kosovtseva, V.Yu. Petrov
Year of manufacture: 2010
Genre: Computer Science, Economics
Publisher: SPGU ITMO
Russian language
Number of pages: 82
Description: The version of MS Excel 2007 is considered. The textbook is intended for students of the specialties "Applied Informatics in Economics" and "Economics" studying the discipline "Informatics", and contains the necessary theoretical information on the use of spreadsheets for solving specific mathematical and economic problems, constructing diagrams , list processing. To uncover

16
Aug
2014

Microsoft Excel 2010. The essentials (Nikita Kultin, Larisa Tsoi)

ISBN: 978-5-9775-0583-3
Format: PDF, eBook (originally computer)
Author: Nikita Kultin, Larisa Tsoi
Year of manufacture: 2010
Genre: Computer literature
Publisher: BHV-Petersburg
Series: Essentials
Russian language
Number of pages: 208
Description: The book is a practical guide for those who are starting to work with Microsoft Excel 2010. It provides the most necessary information for solving typical problems: performing calculations, formatting and designing tables, constructing charts and graphs. Attention is paid to data processing, the use of templates and macros. The book is excellent...

27
Apr
2016

Fundamentals of Finance with Examples in Excel (Shimon Benninga)

ISBN: 978-5-8459-1545-0
Format: DjVu, OCR without errors
Author: Shimon Benninga
Translator: Dmitry Klyushin
Year of manufacture: 2014
Genre: Computer literature
Publisher: Williams
Russian language
Number of pages: 960
Description: This book is the first textbook in which the Excel program is an integral part of financial education. The book is an extremely useful source of knowledge and combines pedagogical techniques and extensive use of Excel's high potential. In today's business world, almost all calculations are performed using Excel. Ability to build charts,...

06
May
2012

User work in Microsoft Excel 2010 (Zudilova T.V., Odinochkina S.V., Osetrova I.S., Osipov N.A.)

Format: PDF, eBook (originally computer)
Author: Zudilova T.V., Odinochkina S.V., Osetrova I.S., Osipov N.A.
Year of manufacture: 2012
Genre: Textbooks, reference books, encyclopedias
Publisher:
SPb.: NRU ITMO
Russian language
Number of pages: 87
Description: The manual provides a guide to the basic techniques of working in Microsoft Excel 2010 in the discipline "Introduction to the Specialty". Intended for students studying in all bachelor's training profiles in the direction: 210700 "Infocommunication technologies and communication systems."
You will be able to: master the MS Excel interface; enter and change information on...