28
jul
2009

Excel 2007

ISBN: 978-5-699-24209-2
Format: PDF, Scanned pages
Year of issue: 2008
Igor Paschenko
Genre: Educational literature
Publisher: EKSMO
Number of pages: 496
Description: IT Literature - Office Applications - Excel 2007
This book will introduce you to the basic features of Microsoft Excel 2007 for creating and managing spreadsheets. You will be able to quickly master all the necessary techniques, since all the necessary material is presented as concisely 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 diagrams and graphs, perform analysis and data processing, and prepare tables for printing. You will be able to do this and much more after reading the book by I. Paschenko 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 it? 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

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

Author: Novikovskiy E.A.
Year of issue: 2012
Genre: Computer literature
Publisher: AltSTU
Russian language
Number of pages: 230
Description: This teaching aid is devoted to the 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 acquire basic skills while working with them. It includes many images and notes that improve the perception and memorization of information. At the end of each of the chapters, checklists are provided to guide you through ...

27
june
2015

Excel - ready-made solutions (Nikolay Pavlov)

ISBN: 978-5-519-01837-1
Format: PDF, eBook (originally computer)
Author: Nikolay Pavlov
Year of issue: 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 face in their daily work. The material of the book covers all the main areas and topics: working with text and dates, formatting, calculations using formulas and functions, processing large data arrays and creating an analogue for 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 issue: 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 can 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 performing calculations (from solving algebraic equations to using with ...

16
jul
2015

Excel 2013. User's Bible (John Walkenbach)

ISBN: 978-5-8459-1872-7
Format: DjVu, Scanned pages
By John Walkenbach
Translator: N. Voronina
Year of issue: 2015
Genre: Computer literature
Publisher: Dialectics
Series: Professional Library
Russian language
Number of pages: 928
Summary: Master the latest version of Excel with an excellent guide - the Excel 2013 Bible for the user! Whether you want to create charts, import and manipulate data, automate tasks with VBA macros, or work in the cloud while still working with Excel, John Walkenbach will tell you how ...

04
oct
2013

A self-explanatory tutorial for Excel 2013 (Alexey Lebedev)

ISBN: 978-5-496-00786-3
Format: PDF, OCR without errors
Author: Alexey Lebedev
Year of issue: 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 demanded office programs. The book is written in a simple, accessible language, provided with practical examples and tasks for self-mastering the material and is intended for a wide range of users. The publication considers the interface of the latest version of Excel, highlights the organization of Excel workbooks, formatting ...

10
aug
2014

Microsoft Office Excel 2010 (Victor Dolzhenkov, Alexander Stuchenkov)

ISBN: 978-5-9775-0594-9
Format: PDF, eBook (originally computer)
Authors: Victor Dolzhenkov, Alexander Stuchenkov
Year of issue: 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. Reflects changes and innovations made by Microsoft in the latest version. The complete cycle of data processing, presentation and analysis is described. Consider ...

15
may
2011

A self-explanatory tutorial Excel 2010 (V. Volkov)

ISBN: ISBN 978-5-49807-771-0
Format: PDF, Scanned pages
Author: V. Volkov
Year of issue: 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 work automation tools, document preparation and rich computing capabilities. The mystery is that most users use only a fraction of what Excel can give them. This is all the more surprising since the range of program features is pr ...

10
aug
2014

Excel 2010 by example (Alexey Vasiliev)

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

16
june
2014

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

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

Publisher: University textbook, Infra-M
Russian language
Number of pages: 336
Description: The capabilities of the spreadsheet processor Microsoft Excel 2010 are described in detail. The technology of analysis and solution of financial and economic problems is described in detail, using numerous examples, figures, tables, diagrams and diagrams. Variants of laboratory and control works and independent tasks for closing ...

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 issue: 2012
Genre: Computer literature
Publisher: Kazan University
Russian language
Number of pages: 100
Description: The capabilities of the popular office application Microsoft Office Excel 2010 are shown with specific examples. In the tutorial you will find: First acquaintance; Elementary data sorting; Graphical presentation of table data; Working with diagrams; Use of 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
By John Walkenbach
Translator: N. Voronin
Year of issue: 2013
Genre: Computer literature
Publisher: Dialectics
Russian language
Number of pages: 912
Description: This book is intended for both beginner and intermediate spreadsheet developers and users, and advanced users who want to learn more about the capabilities of Excel in general and about the nuances of working with the new version of Excel - Excel 2010 - in particular. Written by an Excel guru, this book will show you how to use Excel 2010 to work with spreadsheets, ...

08
feb
2017

MS Excel in computational 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 issue: 2010
Genre: Computer science, economics
Publisher: SPGU ITMO
Russian language
Number of pages: 82
Description: The version of MS Excel 2007 is considered. The manual 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 to solve specific mathematical and economic problems, building diagrams processing lists. To uncover

16
aug
2014

Microsoft Excel 2010. Most Necessary (Nikita Kultin, Larisa Tsoi)

ISBN: 978-5-9775-0583-3
Format: PDF, eBook (originally computer)
Author: Nikita Kultin, Larisa Tsoi
Year of issue: 2010
Genre: Computer literature
Publisher: BHV-Petersburg
Series: The Most Necessary
Russian language
Number of pages: 208
Description: The book is a practical guide for those who begin to work with Microsoft Excel 2010. It provides the most necessary information for solving typical tasks: performing calculations, formatting and formatting tables, building 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
By Shimon Benninga
Translator: Dmitry Klyushin
Year of issue: 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 teaching finance. The book is an extremely useful source of knowledge and combines pedagogical techniques and extensive use of the high potential of Excell. In today's business world, almost all calculations are done with Excel. The ability to build graphs, check ...

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 issue: 2012
Genre: Textbooks, reference books, encyclopedias
Publisher:
SPb .: NRU ITMO
Russian language
Number of pages: 87
Description: The tutorial provides a guide to the basic techniques of working in Microsoft Excel 2010 in the discipline "Introduction to the specialty". It is intended for students studying in all profiles of training bachelors of the direction: 210700 "Infocommunication technologies and communication systems".
You will be able to: master the MS Excel interface; enter and change information on p ...

The book of the main guru Excel John Walkenbach. A weighty Talmud that makes no sense to read from cover to cover - refer to it as a reference for any question. Exactly that for any (well, almost) - a very wide coverage of topics. There's even a section on macros basics to get a basic understanding of them. Formulas and graphs are discussed in detail.

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

In the Russian edition, the 2010 version was made on slightly worse paper, but with better screenshots.

I want to acknowledge the great work of the Russian publishers of Walkenbach's books - they localized all the examples and files.

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

Suitable if you already know the basics and want to delve into pivot tables, formulas, data analysis. The book contains examples and a detailed description of basic Excel functions, array formulas, complex and little-known formulas (for example, RAZDAT, 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 - that, perhaps, the most valuable are advanced formulas created from several functions (such as a formula that allows you to find the occurrence of some text in a cell -)

Although short Excel fundamentals are given here too - so if you're not entirely new, the first workbook can be ditched in favor of this one.

A few examples of useful formulas from the book:

The formula for calculating the quarter number by date:

\u003d ROUNDUP (MONTH (A1) / 3,0)

Counting the number of unique values \u200b\u200bin an interval (array formula):

(\u003d SUM (1 / COUNTIF (Interval; Interval)))

Search for the nearest value in the range (looking for the number closest to the "Target" from the "Data" array)

(\u003d INDEX (Data; SEARCH (SMALL (ABS (Target-Data); 1); ABS (Target-Data); 0)))

The effect of alternating lines in conditional formatting:

Create a condition for the formula:

\u003d REST (ROW (), 2) \u003d 0

and paint the cells with the desired color.

There are hundreds of such examples in the book. My copy is all in sticky notes - it marked interesting and useful ideas.

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

The most complete and quality guide to creating macros in Excel. She helped out in many situations, helped a lot to create complex projects in VBA (a la the automatic creation of analytical reports in Word based on management reporting in Excel).

Complete 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 "typing method" with the information from the book.

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

In 95% of cases, I manage to find the answer to an Excel question in one of these three Walkenbach books. His books are more voluminous and more expensive than the countless "tutorials" on the market. But it is better to invest in them once and have high-quality information at hand than to be guided by the seeming 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", ready-made solutions.

For fundamental mastering Excel, unlike the first book on the list, is not suitable, but with a high probability it will help to quickly solve the current work problem. The book contains 100 tips like "How to Make Two Charts the Same Size", "Detecting Extra Spaces" and "Removing Minus Signs from Negative Numbers."

Note that the VBA language changes little and does not depend on versions. For a long time I used the book "Excel 2003. Professional VBA Programming" and only by the release of the 2013 version I got a new edition. So in the case of this book, do not be confused by 2010 in the title.

There are tons of useful articles and video tutorials. By the way, the author of this site has his own book with ready-made techniques and solutions. And it comes with sample files. Recommend.

Working with Excel: a tutorial. Excel (Excel) - one of the basic programs of the Microsoft Office suite. It is an irreplaceable assistant when working with invoices, reports, tables.
Excel (Excel) allows you to:
program, store huge amounts of information
Build graphs and analyze results
Make calculations quickly
This program is an excellent choice for office work.
Getting started with Excel (Excel)
1. Double-clicking on the sheet name, enter the editing mode. In this panel, you can add a new sheet to the book, delete an unnecessary one. It's easy to do - you need to right-click and select the "Delete" line.

2. It is easy to create another book - select the New line in the File Menu. The new book will be placed on top of the old one, and an additional tab will appear on the taskbar.

Working with tables and formulas
3. An important function of Excel (Excel) is convenient work with tables.

Thanks to the tabular form of data presentation, tables automatically turn into a database. It is customary to format tables, for this we select the cells and set them separate properties and format.

In the same window, you can perform alignment in a cell, this is done by the "Alignment" tab.

The Font tab has the option to change the font of the text in a cell, and the Insert Menu lets you add or remove columns, rows, and more.

Moving cells is easy - the "Cut" icon on the Home tab will help.

4. No less important than the ability to work with tables is the skill of creating formulas and functions in Excel.

Simple F \u003d ma is a formula, force is equal to the product of mass and acceleration.

To write such a formula in Excel (Excel), you must start with the "\u003d" sign.

Print document
5. And the main stage after the completed work is the printing of documents.

MINISTRY OF EDUCATION AND SCIENCE OF THE RUSSIAN FEDERATION

STATE EDUCATIONAL INSTITUTION OF HIGHER PROFESSIONAL EDUCATION

ROSTOV STATE CONSTRUCTION UNIVERSITY

Approved at a meeting of the Department of ISS

Methodical instructions

and control tasks in the discipline "Informatics" Parts 1 and 2

for bachelors of extramural education

Rostov-on-Don

Extended capabilities of Microsoft Excel 2007. Solution of practical problems.

annotation

This tutorial describes the advanced features of Microsoft Office Excel 2007, a program included in the Microsoft Office 2007 software package (Russian version)

General information ................................................ .................................................. ....................................
Data input................................................ .................................................. ............................................
Formulas ................................................. .................................................. .................................................
Filling cells with lists ............................................... .................................................. ...................
Data validation................................................ .................................................. ...................................
Conditional Formatting ................................................ .................................................. ....................
Calculations ................................................. .................................................. ............................................
Working with databases .............................................. .................................................. ..........................
Summary tables ................................................ .................................................. ...................................
Summary charts ................................................ .................................................. ...............................
Selection of parameter ................................................ .................................................. .................................
Finding a solution ................................................ .................................................. ....................................
Protection of cells, sheets and workbooks ........................................... .................................................. ..
Tasks................................................. .................................................. ..................................................

General information

Sheet dimensions

IN earlier versions of Exceleach sheet has 65,536 rows and 256 columns.

Excel 2007 has more available space - the sheet contains:

Increase sheet size book created in formatExcel 97-2003:

1.open the file in Excel 2007,

2.select Office - Convert, save in Excel 2007 format.

When saving large tables (more than 65,536 rows) in the format of earlier releases of Excel, it is important to remember that data exceeding the sheet size of 65,536 rows and 265 columns will be lost.

To use Excel 2000-2003 to open and save files created in Excel 2007 format, you must install compatibility package(this module can be downloaded from the Microsoft website, installed)

		File formats
*. xls		excel 97-2003 workbook format
*. xla		excel 97-2003 Add-in (VBA, Macros)
*. xlsx		default Excel 2007 workbook format without macro support
*. xlsm -		excel 2007 Macro-Enabled Workbook
*. xlsb		binary format for large amounts of data
*. xltx		excel 2007 workbook template without macro support
*. xltm -		excel 2007 Macro-Enabled Workbook Template
*. xlam -		excel 2007 add-in

Excel limitations

Memory - buffer for formulas and pivot tables increased to 2GB (1GB in Excel 2003) Sort - sort levels increased to 64 (3 in Excel 2003)

AutoFilter - drop down list of up to 10,000 items (1,000 in Excel 2003)

Maximum formula length- up to 8,000 characters (1,000 in Excel 2003) Function nesting- up to 64 (7 in Excel 2003)

Conditional formatting- an infinite number of criteria (3 in Excel 2003) Limit number of formatscells in a workbook - up to 64,000 (4,000 in Excel 2003) Number of colors- up to 4.3 million (56 in Excel 2003)

Number of characters in a cell - up to 32,000 (1,024 in Excel 2003)Number of rollbacks (Undo) - up to 100 (16 in Excel 2003)

Program interface

All tools are grouped on the Ribbon according to the principle of general action. Access to toolboxes - Tabs.

In addition to permanent tabs, tabs dynamically appear associated with the selected object (such tabs are called contextual tabs).

On each tab, tools are also grouped into sets of related commands - groups. If a group of tools has a corresponding dialog box, then to call it, press the button in the lower right part of the group. The ribbon can be hidden / shown by double clicking on the active tab.

The Office button is a new view of the File menu, contains new features along with the previous commands of the File menu.

In the Office menu, the Excel Options button opens the program settings window, which includes three familiar windows from the previous versions: Tools –Options,

Add ins, Customize.

Adding buttonsto the Quick Access panel:

1. Select Office - Excel Options(Excel Options),

2. Go to categorySetting (Customize),

3. In the upper list, select a group of commands,

4. Choose a tool;

5. Click Add, then OK.

New opportunities

Manual input

When entering a formula manually, a prompt appears listing the names of cells and functions. To select, double-click on the name. When you select a function, a brief description of the arguments appears.

Hotkeys

If you press ALT, then on the screen prompts will appear for selecting commands without using the mouse. The keys must be pressed consistently.

CTRL + N - Create,

CTRL + O - Open,

View Mode Page Layout View

In this mode the table will be shown as it will be printed. Margins, headers and footers, ruler, pagination are displayed, allowing the user to work with a table oriented to further printing.

To switch to Page Layout mode, you need to:

1. Select the View tab;

2. In a group Book view modes(Workbook Views) select Page layout(Page Layout).

Another way to enter this mode is to select the button Page layout(Page Layout) on the right side of the status bar of the program window.

Status bar

As before, it is located at the bottom of the program window. Easy to customize - prompt options can be configured by right-clicking on the status bar. This is where the zoom button and slider appear

Live Preview

When decorating a table, the function Live viewingmakes it possible to see the final result before the very application of the command. This saves time that previously was spent canceling an unsuccessful action.

To use the preview of variants, you must stop the cursor on the desired variant. The selected area changes to show the selection. To apply a suitable option, click on it. This feature can be disabled in the Popular section of the Excel Options window.

Data input

With cells

IN each cell of the sheet can contain:

1. TEXT (the default alignment is the left edge of the cell)

2. NUMBER (the default alignment is the right edge of the cell)

a. Normal (in the Russian locale the decimal separator is a comma, and in English it is a period)

Without changing the system locale, you can configure Excel to use a different decimal separator. For

this must be turned off in the Advanced section of the Excel Options window Use system separators

(Use system separator), enter the separator character.

To represent numbers in Excel uses 15 digits... If you enter a number that is more than 15 digits long (both before and after the decimal point), Excel will save it to 15 digits, replacing the remaining digits with zeros.

Very large numbers are automatically represented in exponential format: 1.2 E + 19 \u003d 1.2 * 1019 or 1.2 E19 \u003d 1.2 * 1019. The largest positive number is 9.9 E + 307; the smallest positive number is 1 E307 (numbers greater than 9.9 E + 307 become text, less than 1 E307 - zero).

b. Date (start date - 01/01/1900, end - 12/31/9999).

The date separator is a day / month / year slash or a period.

01.01.1900=1, 02.01.1900=2, … 31.12.9999=2 958 465

You can change the first date to 01/02/1904 by including Use the 1904 date system(Use 1904 date system) Advanced section

(Advanced) of the Excel Options window.

c. Time (from 00:00:00 to 23:59:59) is a fractional part of the day: 12:00 \u003d 0.5.

If the number of hours is more than 23, the entered time is converted to date-time format. For example, 24:12:15 \u003d 01.01.1900 0:12:15

You can change the system locales in the Control Panel (Start –Settings –Control Panel) in the Regional and Language Options group. These settings affect the operation of programs and include, in addition to numbers, the currency, the form of recording the date and time.

3. FORMULA. Formulas begin with an \u003d (equal) sign, can contain parentheses (priority of operations), cell references, cell and range names, operation signs, function calls, etc.

Cell range: A1: A25 (everything from A1 to A25, character: colon)

Range of cells:A1: C25 (all A1 to A25, B1 to B25, C1 to C25)

Cell group: A1; A25 (2 cells A1 and A25, character; semicolon)

By default, the cell with the formula displays the answer (number or text),

in Formula Bar - Formula. This mode can be changed by enabling

Show formulas, not their values (Show formulas in cell instead ...)

in the Advanced section of the Excel Options window (or "Ctrl" + "")

			Operation signs
	Operators			Operation			Result
Arithmetic			Addition
			Subtraction or sign of a negative number
		"star"	Multiplication
		"cap"	Exponentiation
		"percent"	Percentage (1 \u003d 100%, 0.2 \u003d 20%)
Comparisons
			More or equal
							(True) 5\u003e \u003d 0
			Less or equal
							(False) 1<>1
		"Ampersand"	Text operator. Concatenation of strings (\u003d B2 & "rub.")
		"colon"					A1: A100
		"Point from the west."	Combining multiple links into one: A1 and A100				A1; A100
		"space"
			Intersection. Common cells 2 ranges 1: 2 9

				When copying a formula
	Relative
			down - W51, up - W49, right - X50, left - V50
	Absolute
				$ - commit flag
	Mixed		Column does not change, the string can change
			String does not change, the column can be changed

Switching between link types - F4 key on your keyboard:

W50 F4 $ W $ 50 F4 W $ 50 F4 $ W50 F4 W50

Linking sheets of one book

When entering a formulait is necessary:

1. Go to the required sheet (click on the sheet tab);

3. Introduce next operation sign

Linking workbooks

You must first open linked books and When entering a formula:

1. Go to the desired book, sheet(where does the link for the formula come from);

2. Select a cell or a range of cells;

3. Introduce next operation signor Enter (to complete the formula).

On the Data tab, in the Connections group, the

Edit Links will allow you to update, change, break the link ... When a link is broken, formulas and xrefs are irrevocably replaced by their values \u200b\u200b(text or number at the time of the break).

Using names in formulas

Creating names

1 way. Formula bar

2. In the Name field (left side of the formula bar) enter a new name;

3. Press Enter

Name length up to 255 characters. 1st character: letter, _ (underline) or\\ (obverse slash), followed by letters, numbers, periods and underscores. Without spaces ! Names in the form of cell references (for example, Z $ 100 or R1C1) are not allowed.

Method 2. From table row and column headers

1. Select a range, including headers rows and columns;

2. On the Formulas tab, in the Specific names(Defined Names) select team Create from selection(Create from Selection);

3. Specify the location of headers(row above / below, column left / right).

The resulting Names are the same as the names of the first or last row (column) in the range, but only refer to cells below (to the right), above (to the left) of these row (column) headers.

Method 3. Name Manager Dialog Box

1. Select a cell (range);

2. Formulas tab, select Name Manager;

3. Click the New button, in the New Name window that appears:

a. Enter Name,

b. Define Scope

(within a sheet / book),

c. Introduce

If new data appears on the sheet for the constructed chart and 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).

Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (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. Similarly, you can extract names from email, glue names from fragments, and so on.

You are most likely aware of the magic autocomplete marker. This is a thin black cross in the lower right corner of the 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. 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 Fill Without Formatting option, Excel will copy your formula without formatting and will not spoil the layout.

In Excel, you can quickly display your geodata on an interactive map, such as sales by city. To do this, go to the Office Store under the Insert tab and install the Bing Maps plugin from there. This can 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. 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 the number of worksheets in the file has exceeded 10, 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. The table of contents will appear, and you can jump to any desired sheet instantly.

If you've ever had to move cells from rows to columns with your hands, then you will appreciate the following trick:

1. Highlight the range.
2. Copy it (Ctrl + C) or, by clicking on the right mouse button, select "Copy" (Copy).
3. Right-click on 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 such an icon, but you can fix the problem by using Paste Special (Ctrl + Alt + V) and choosing the Transpose option.

If it is supposed to enter strictly defined values \u200b\u200bfrom the allowed set into any cell (for example, only "yes" and "no" or only from the list of company departments, and so on), then this can be easily organized using the drop-down list.

1. Select a cell (or a range of cells) that should have such a limitation.
2. Click the "Validation" button on the "Data" tab (Data → Validation).
3. In the drop-down list "Type" (Allow) select the option "List" (List).
4. In the “Source” field, specify the range containing the reference variants of the elements, which will subsequently drop out when typing.

If you select a range with data and on the "Home" tab click "Format as a 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 stretches 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 filtering and sorting.
4. On the "Design" tab that appears, you can add a totals row with automatic calculation to such a table.

Sparklines are miniature charts drawn right in cells that visualize 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 the sparklines.

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

Imagine: you close the report that you were messing with for the last half of the day, and in the dialog box that appears "Save changes to file?" suddenly for some reason you press "No". The office announces your heart-rending scream, but it's too late: the last few hours of work went down the drain.

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

In Excel 2013, the path is slightly different: File → Info → Version Control → File - Properties - Recover Unsaved Workbooks.

In subsequent versions of Excel, open File → Info → Workbook Management.

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

Sometimes when working in Excel, you need to compare two lists and quickly find items that are the same or different in them. Here's the quickest and most intuitive way to do it:

1. Select both compared columns (hold down the Ctrl key).
2. Select Home → Conditional formatting → Highlight Cell Rules → Duplicate Values \u200b\u200bon the Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
3. Select the Unique option from the dropdown list.

Ever tinker with the input values \u200b\u200bin 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 "undershoot - overflights" - and here it is, the long-awaited hit!

Microsoft Excel can do this for you, faster and more accurately. To do this, click the "What If" 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 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.