How to create an excel file, open, save, close. We send Excel sheets to the Internet, share them, paste them into a web page and make them interactive How to create a new document in Excel

The task of creating a one-time Excel file in 1C or organizing a constant exchange of data between these programs arises for most accountants. You can solve it yourself with the help of simple commands and codes, or - in more complex situations - with the assistance of specialists.

Working with tables in Excel and 1C

The 1C program up to version 8 had very limited functionality for working with tables. Therefore, many users preferred, and the owners of version 1C 7.7 still prefer to save the 1C file with the .xls extension, in order to then modify it in a more convenient Excel. This method allows you to use strengths both programs and perform the necessary calculations in the most comfortable environment.

The only thing to keep in mind is minor inconsistencies in tabular formats. So, the Microsoft table editor perceives the entry of a fractional number only if there is a separator in the form of a comma (1.5, for example). Therefore, before loading a table from 1C, replace other separators (slash or dot) with commas. Otherwise, the table editor will not be able to perform calculations with fractional numbers, and sometimes will not even perceive them as such, but will convert them to dates.

There is also the opposite problem, when 1C does not "understand" constants Microsoft programs and prints them out as numbers. To translate them back into parameters, you need to go to help or debug spreadsheet editor and find there a numerical expression of various constants.

File upload to Excel

The easiest way to create an Excel file in 1C is to put the document into print mode and then save it in the .xls table extension. The * must be replaced by the file name. By analogy with this method, instead of a printed form, you can display data in a layout and save it in a table extension.

Another way is to use OLE and related codes:

Open document:

• Book=Excel.WorkBooks.Open(PathToFile).

Create new file:

• An exception

  Report(Error Description() + "Excel is not installed on this computer!");

• End of Attempt;

  Book=Excel.WorkBooks.Add().

Select sheet:

• Sheet=Book.WorkSheets(SheetNumber).

Uploading a file in 1C

Now let's look at how to transfer data from Microsoft spreadsheets in 1C again using OLE:

Getting access from 1C to running application Excel:

• Excel= CreateObject("Excel.Application");

  An exception

  Report(ErrorDescription() + "Excel is not installed on this computer!");

• EndTry.

With subsequent access to:

• specific document:

  • Book=Excel.WorkBooks.Open(PathToFile);

    PathToFile - full path to Excel file;

• Sheet=Book.WorkSheets(SheetName);

• Value=Sheet.Cells(RowNumber,ColumnNumber).Value.

In order not to overload the memory and processor of the computer, after executing the necessary commands, you should run the Excel.Quit(); code. It will terminate the application.

If you have any difficulties using the codes, qualified consultants are always ready to help you. Experts can help you solve the problem of moving a large amount of data or organizing a constant exchange of data between two applications. The necessary operations will be debugged at the program level.

The Microsoft Excel program is convenient for drawing up tables and making calculations. A workspace is a set of cells that can be filled with data. Subsequently - to format, use to build graphs, charts, summary reports.

Working in Excel with tables for beginners may seem daunting at first glance. It differs significantly from the principles of constructing tables in Word. But we'll start small: creating and formatting a table. And at the end of the article, you will already understand that the best tool to create tables than Excel can imagine.

How to Create a Table in Excel for Dummies

Working with spreadsheets in Excel for dummies is not rushed. You can create a table different ways and for specific purposes, each method has its own advantages. Therefore, first visually assess the situation.

Look carefully at the spreadsheet worksheet:

This is a set of cells in columns and rows. Basically a table. The columns are marked with Latin letters. Rows are numbers. If we print this sheet, we get a blank page. Without any boundaries.

First, let's learn how to work with cells, rows and columns.

How to select a column and row

To select the entire column, click on its name (Latin letter) with the left mouse button.

To select a line - by line name (by number).

To select multiple columns or rows, left-click on the title, hold and drag.

To select a column using hot keys, place the cursor in any cell of the desired column - press Ctrl + space. To select a line - Shift + Space.

How to change cell borders

If the information does not fit when filling out the table, you need to change the borders of the cells:

To change the width of columns and the height of rows at once in a certain range, select an area, increase 1 column / row (move manually) - the size of all selected columns and rows will automatically change.

Note. To return former size, you can click the "Cancel" button or the hot key combination CTRL + Z. But it works when you do it right away. Later will not help.

To return lines to their original borders, open the tool menu: "Home" - "Format" and select "Autofit Line Height"

For columns, this method is not relevant. Click "Format" - "Default Width". Let's remember this number. We select any cell in the column whose borders need to be "returned". Again "Format" - "Column Width" - enter given by the program indicator (usually it is 8.43 - the number of characters in the Calibri font with a size of 11 points). OK.

How to insert a column or row

Select the column/row to the right/below where you want to insert the new range. That is, the column will appear to the left of the selected cell. And the line is above.

Click right click mouse - select "Insert" in the drop-down menu (or press the hot key combination CTRL + SHIFT + "=").

Select the column and click OK.

Advice. For quick insert column, select the column in the desired location and press CTRL+SHIFT+"=".

All these skills will come in handy when compiling a table in Excel program. We will have to expand the boundaries, add rows / columns in the process.

Step by step creation of a table with formulas

Now, when printing, the borders of columns and rows will be visible.

Using the Font menu, you can format Excel spreadsheet data as you would in Word.

Change, for example, the font size, make the header “bold”. You can set the text to the center, assign hyphenation, etc.

How to create a table in Excel: step by step instructions

The simplest way to create tables is already known. But in Excel there is a more convenient option (in terms of subsequent formatting, working with data).

Let's make a "smart" (dynamic) table:

Note. You can go the other way - first select a range of cells, and then click the "Table" button.

Now enter the necessary data into the finished frame. If you need an additional column, put the cursor in the cell intended for the title. Enter a name and press ENTER. The range will automatically expand.

If you need to increase the number of lines, hook in the lower right corner of the autocomplete marker and drag it down.

How to work with a table in Excel

With the release of new versions of the program, work in Excel with tables has become more interesting and dynamic. When a smart table is formed on the sheet, the tool "Working with tables" - "Designer" becomes available.

Here we can name the table, change the size.

Various styles are available, the ability to convert the table to a regular range or summary report.

Features of dynamic spreadsheets MS Excel are huge. Let's start with basic data entry and autocomplete skills:

If you click on the arrow to the right of each header subheading, then we will get access to additional tools for working with table data.

Sometimes the user has to work with huge tables. To see the results, you need to scroll through more than one thousand lines. Deleting rows is not an option (the data will be needed later). But you can hide. For this purpose, use numerical filters (picture above). Uncheck the boxes next to the values ​​that should be hidden.

As part of the first material Excel 2010 for beginners, we will get acquainted with the basics of this application and its interface, learn how to create spreadsheets, as well as enter, edit and format data in them.

Introduction

I think I will not be mistaken if I say that the most popular application included in the package Microsoft office, is a test editor (processor) Word. However, there is another program, without which any office worker rarely does. Microsoft Excel (Excel) refers to software products called spreadsheets. With the help of Excel, in a visual form, you can calculate and automate the calculations of almost anything, from your personal monthly budget to complex mathematical and economic-statistical calculations containing large amounts of data arrays.

One of key features spreadsheets is the ability to automatically recalculate the value of any desired cells when the content of one of them changes. To visualize the received data, based on groups of cells, you can create different kinds charts, pivot tables and maps. At the same time, spreadsheets created in Excel can be inserted into other documents, as well as saved in separate file for later use or editing.

Calling Excel simply a “spreadsheet” would be somewhat incorrect, since this program has huge capabilities, and in terms of its functionality and range of tasks, this application, perhaps, can surpass even Word. That is why, as part of the Excel for Beginners series of materials, we will only get acquainted with the key features of this program.

Now, after the end of the introductory part, it's time to get down to business. In the first part of the cycle, for better assimilation of the material, as an example, we will create a regular table reflecting personal budget expenses for six months of this type:

But before we start creating it, let's first look at the main elements of the interface and Excel controls and also talk about some basic concepts this program.

Interface and control

If you are already familiar with Word editor, then understanding the Excel interface is not difficult. After all, it is based on the same ribbon, but only with a different set of tabs, groups, and commands. At the same time, in order to expand the working area, some groups of tabs are displayed only when necessary. You can also collapse the ribbon altogether by double-clicking on the active tab with the left mouse button or by pressing the Ctrl + F1 key combination. Returning it to the screen is carried out in the same way.

It is worth noting that in Excel, for the same command, several ways of calling it can be provided at once: through the ribbon, from context menu or using a keyboard shortcut. Knowing and using the latter can greatly speed up the work in the program.

The context menu is context-sensitive, that is, its content depends on what the user does in this moment. The context menu is invoked by pressing the right mouse button on almost any object in MS Excel. This saves time because it displays the most frequently used commands for the selected object.

Despite such a variety of controls, the developers went further and provided users in Excel 2010 with the ability to make changes to the built-in tabs and even create their own with those groups and commands that are used most often. To do this, right-click on any tab and select Ribbon customization.

In the window that opens, in the menu on the right, select the desired tab and click the button Create tab or To create a group, and in the left menu the desired command, then click the button Add. In the same window, you can rename existing tabs and delete them. There is a button to cancel erroneous actions. Reset, which returns the settings of the tabs to the initial ones.

Also, the most frequently used commands can be added to Panel quick access located in the upper left corner of the program window.

You can do this by clicking on the button Customizing the Quick Access Toolbar, where it is enough to select the desired command from the list, and if the necessary one is not in it, click on the item Other commands.

Entering and editing data

The files created in Excel are called workbooks and have the extension "xls" or "xlsx". In turn, the workbook consists of several worksheets. Each worksheet is a separate spreadsheet that can be interconnected if necessary. The active workbook is the one you are currently working with, such as the one you are entering data into.

After starting the application, a new book is automatically created with the name "Book1". By default, a workbook consists of three worksheets named "Sheet1" through "Sheet3".

The working field of the Excel sheet is divided into many rectangular cells. Cells merged horizontally make up rows, and merged vertically make up columns. To be able to study a large amount of data, each worksheet of the program has 1,048,576 rows numbered with numbers and 16,384 columns marked with letters of the Latin alphabet.

Thus, each cell is the intersection of the various columns and rows on the sheet, forming its own unique address, consisting of the column letter and row number to which it belongs. For example, the name of the first cell is A1 because it is at the intersection of column "A" and row "1".

If the application is enabled Formula bar, which is located immediately below Ribbon, then to the left of it is Name field, which displays the name of the current cell. Here you can always enter the name of the desired cell, for a quick transition to it. This feature is especially useful in large documents containing thousands of rows and columns.

Also, to view different areas of the sheet, scroll bars are located at the bottom and on the right. You can also navigate the Excel workspace using the arrow keys.

To start entering data in the desired cell, it must be selected. To go to the required cell, click on it with the left mouse button, after which it will be surrounded by a black frame, the so-called active cell indicator. Now just start typing on the keyboard, and all the information you enter will appear in the selected cell.

When entering data into a cell, you can also use the formula bar. To do this, select the desired cell, and then click on the formula bar field and start typing. In this case, the entered information will be automatically displayed in the selected cell.

After completing the data entry, press:

• "Enter" key - next active cell will be a cage at the bottom.
• The "Tab" key - the cell on the right will become the next active cell.
• Click on any other cell and it will become active.

To change or delete the contents of any cell, double-click on it with the left mouse button. Move the blinking cursor to the desired location to make the necessary edits. Like many other applications, the arrow keys, "Del" and "Backspace" are used to delete and make corrections. If desired, all the necessary edits can be made in the formula bar.

The amount of data that you will enter into a cell is not limited to its visible part. That is, the cells of the working field of the program can contain both one number and several paragraphs of text. Each Excel cell can hold up to 32,767 numeric or text characters.

Formatting cell data

After entering the names of the rows and columns, we get a table of the following form:

As can be seen from our example, several names of expense items “went” beyond the boundaries of the cell, and if the neighboring cell (cells) also contains some information, then the entered text is partially overlapped by it and becomes invisible. And the table itself looks rather ugly and unpresentable. At the same time, if you print such a document, then the current situation will continue - it will be quite difficult to disassemble in such a table what is what, as you can see for yourself from the figure below.

To do spreadsheet document neater and more beautiful, often you have to change the size of rows and columns, the font of the contents of the cell, its background, text alignment, add borders, and more.

First, let's tidy up the left column. Move the mouse cursor to the border of columns "A" and "B" in the line where their names are displayed. When changing the mouse cursor to a characteristic symbol with two multidirectional arrows, press and hold the left key, drag the dashed line that appears in the desired direction to expand the column until all the names fit within one cell.

The same actions can be done with a string. This is one of the easiest ways to resize the height and width of cells.

If you need to set the exact sizes of rows and columns, then on the tab home in a group cells select item Format. In the menu that opens, using the commands Row height And Column Width you can set these parameters manually.

Very often it is necessary to change the parameters of a rhinestone of several cells and even an entire column or row. To select an entire column or row, click on its name at the top or on its number on the left, respectively.

To select a group of adjacent cells, circle them with the cursor, hold down the left mouse button. If you need to select scattered fields of the table, then press and hold the "Ctrl" key, and then click on the necessary cells with the mouse.

Now that you know how to select and format multiple cells at once, let's center the months in our table. Various commands for aligning content inside cells are on the tab home in a group with a talking name alignment. At the same time, for a table cell, this action can be performed both relative to the horizontal direction and vertical.

Circle the cells with the name of the months in the table header and click on the button Align Center.

In a group Font tab home You can change the font type, size, color, and style: bold, italic, underline, and so on. There are also buttons for changing the cell borders and its fill color. All these functions will be useful to us to further change the appearance of the table.

So, for starters, let's increase the font of the column and column names of our table to 12 points, and also make it bold.

Now select first the top line of the table and set it to black background, and then in the left column to cells from A2 to A6 - dark blue. You can do this using the button Fill color.

Surely you noticed that the color of the text in the top line has merged with the background color, and the names in the left column are poorly readable. Let's fix this by changing the font color with the button Text color on white.

Also with the help of the already familiar command Fill color we've given the background of the even and odd number rows a different blue tint.

So that the cells do not merge, let's define borders for them. The definition of borders occurs only for the selected area of ​​the document, and can be done both for one cell and for the entire table. In our case, select the entire table, then click on the arrow next to the button Other borders all in the same group Font.

The menu that opens displays a list of quick commands with which you can choose to display the desired boundaries of the selected area: bottom, top, left, right, outer, all, and so on. It also contains commands for drawing borders manually. At the very bottom of the list is Other borders allowing you to set the necessary parameters of cell borders in more detail, which we will use.

In the window that opens, first select the type of border line (in our case, a thin solid one), then its color (we will choose white, since the background of the table is dark) and finally, those borders that should be displayed (we chose internal ones).

As a result, using a set of commands of just one group Font we have transformed the unsightly appearance tables into quite presentable, and now knowing how they work, you can independently invent your own unique styles for designing spreadsheets.

Cell Data Format

Now, in order to complete our table, it is necessary to properly format the data that we enter there. Recall that in our case, these are cash costs.

In each of the cells spreadsheet you can enter different types data: text, numbers and even graphics. That is why in Excel there is such a thing as a "cell data format", which serves to correctly process the information you enter.

Initially, all cells have General format, allowing them to contain both textual and numeric data. But you have the right to change this and choose: numeric, monetary, financial, percentage, fractional, exponential and formats. In addition, there are formats for date, time, postal codes, phone numbers, and personnel numbers.

For cells in our table that contain the names of its rows and columns, the general format (which is set by default) is quite suitable, since they contain text data. But for cells in which budget expenses are entered, the monetary format is more suitable.

Select the cells in the table containing information on monthly expenses. On the ribbon in a tab home in a group Number click the arrow next to the field Numeric Format, after which a menu with a list of the main available formats will open. You can select item Monetary right here, but for a more complete review, we will select the very bottom line Other number formats.

In the window that opens, in the left column, the names of all number formats, including additional ones, will be displayed, and in the center, various settings for their display.

By selecting the currency format, at the top of the window you can see how the value will look in the cells of the table. Just below the mono set the number of decimal places to display. So that pennies do not clutter up the table fields, we set the value here to zero. Next, you can choose the currency and display negative numbers.

Now our training table is finally complete:

By the way, all the manipulations that we did with the table above, that is, the formatting of cells and their data, can be performed using the context menu by right-clicking on the selected area and selecting Cell Format. In the window that opens with the same name, there are tabs for all the operations we have considered: Number, alignment, Font, Border And fill.

Now, when you finish working in the program, you can save or print the result. All these commands are in the tab File.

Conclusion

Probably, many of you will ask yourself: “Why create this kind of table in Excel, when all the same can be done in Word using ready-made templates? That's how it is, only to perform all kinds of mathematical operations on cells in text editor impossible. You almost always enter the information in the cells yourself, and the table is just a visual representation of the data. Yes, and voluminous tables in Word are not very convenient to do.

In Excel, the opposite is true, tables can be arbitrarily large, and cell values ​​can be entered either manually or automatically calculated using formulas. That is, here the table acts not only as a visual aid, but also as a powerful computational and analytical tool. Moreover, cells can be interconnected not only within one table, but also contain values ​​obtained from other tables located on different sheets and even in different books.

How to make a table "smart" you will learn in the next part, in which we will get acquainted with the basic computing Excel features, rules for constructing mathematical formulas, functions and much more.

For a number of users who work closely with Excel spreadsheets, you may need to create an excel spreadsheet on a computer that does not have Microsoft Office installed (and therefore does not have Microsoft Excel). Problem? Not at all! Popular online services will help you create this table, allowing you to easily create an Excel spreadsheet online. In this article I will tell you how to run Excel online, what services will help us with this, and how to work with them.

Specifics of services with embedded Excel

There are several popular network services, allowing not only to open an Excel file online, but also to create new table Excel, edit it, and then save it to your PC. Usually they have a functionality quite similar to MS Excel, and working with them will not create any problems for users who have previously dealt with the stationary MS Excel editor.

To register on such platforms, you can use the login and password of the user account in in social networks(for example, on Facebook), or go standard procedure registration via email.

Use the functionality of Excel

I like to work with documents, spreadsheets and create presentations online without installing many programs on my computer, so I created a selection the best services- And .

Let's move on to the list of services that allow you to create Excel spreadsheets on the Internet online. Some of them are quite active with cloud services, therefore, they can copy the tables created by the user there, and from there the user can download the table he created to his computer.

Office.Live - a program for creating and editing spreadsheets

This service from Microsoft gives you the opportunity to access the features of MS Office online, and it's completely free. In particular, you can use the functionality of the network MS Excel spreadsheet editor, which allows you to create, edit and save tables online in popular formats of this editor (, xml and others).

1. To work with the editor, go to the indicated resource https://office.live.com/start/Excel.aspx .
2. Select "Sign in with a Microsoft account".
3. And create account(or use your Skype account credentials).
4. Next, you will be taken to the basic templates screen for creating an excel spreadsheet. Click on "New Book" and you will enter the mode of creating and editing your spreadsheet.
5. To save the result, click on the tab "File" - "Save as" - "Download a copy".

Google Sheets - allows you to add graphs and charts to Excel

Google Docs is a web online service from Google with rich features, including allowing you to work with excel, view relevant file formats (XLS, XLSX, ODS, CSV), create graphs and charts, and so on. To work with this service, you must have a Google account, but if you do not have one, then I suggest you create one.

1. To take advantage Google capabilities Docs go to this resource https://docs.google.com/spreadsheets/u/0/ and enter your Google account details.
2. To create a new document, click on "Blank" (a rectangle with a green plus sign), and you will enter the Excel spreadsheet creation mode.
3. The service interface is in English, but for users who have previously dealt with Excel, working with Google Docs will not cause any problems.
4. To save the table you created to your computer, click on "File" - "Download as" and select a convenient file saving format (for example, xlsx).

ZOHO Sheet - spreadsheet software

A service with support for the Russian language, which has all the necessary functionality for creating Excel spreadsheets. The service can do almost everything that its competitors do - it works with most Excel formats, supports joint editing of a document by several users, allows you to build graphs and charts, and so on.

1. https://www.zoho.com/docs/sheet.html
2. Click on the "CREATE SPREADSHEET" button (you may need your Google account details) and you will enter the spreadsheet creation mode.
3. You can save the result by clicking on "File" - "Export as", and choosing the file format that is convenient for you (usually it is "MS Excel book").

EtherCalc - Opens .xlsx, .xlsm and .xls files

Among the advantages of this service, I would note the ability to work with Excel tables without any registration, support for parallel editing of one table by several users, convenient functionality, as well as many other advantages that allow you to work with Excel tables online in Russian.

1. To work with this service, go to https://ethercalc.org/ .
2. Click on the "Create Spreadsheet" button.
3. You will enter edit mode and be able to create the table you need.
4. To save the final document, click on the image with a floppy disk on the left, select the save format, and download the spreadsheet to your PC.

Working window of the "EtherCalc" service

Yandex.Disk - easy work with Excel online

The domestic company Yandex pleased the user with the opportunity to create and edit online document Excel using a special service on Yandex.Disk. In terms of its functionality, this service is a copy of Office Online (the first of the network services I described), while, according to user reviews, on this service from Yandex sometimes there are problems in the operation of large formulas, bugs arise, the service does not work well with Excel files created in MS Excel before 2007.

1. To work with this service, go to it https://disk.yandex.ua/client/disk (you may need to register with Yandex).
2. Click on the "Create" - "Table" button.
3. You will enter the table creation and editing mode.

Conclusion

To work with Excel online, you should use online services I listed above. All of them are free, support (with a few exceptions) a Russian-language interface, and are almost as functional as the stationary version of MS Excel. If the reader urgently needed to create an Excel spreadsheet, but there was no computer with MS Office installed at hand, then you should use the functionality described above network tools, allowing to effectively solve this, and similar to it, problems.

Sharing in Microsoft Excel makes it possible for multiple users to work on the same file at once. Ten to twenty people different computers simultaneously enter some data into one document. Where certain information is located, certain formulas work.

"Main user" has the ability to track activities working group, add/remove members, edit inconsistent changes. How to set up collaboration in Excel.

Features of working with a shared file

Not all tasks can be performed in a shared Excel workbook.

It is forbidden:

• Create Excel tables.
• Create, modify or view scenarios.
• Delete sheets.
• Merge or split cells.
• Work with XML data (import, add, update, delete, etc.).

Exit: disable general access– perform a prohibited task – re-enable access.

Sharing limits a number of other tasks for participants:

How to share an Excel file?

First, we determine which book we will "open" for editing by several participants at once. Create a new file and fill it with information. Or open an existing one.

Attention! Cannot be used to save shared file web server.

Opening a Shared Book

Everything. You can edit information, enter new information. Save after work.

It happens that when you open an Excel workbook with file sharing, the entry "File is locked" appears. Save fails. The next time you open it, it turns out that sharing is disabled. Possible reasons Problems:

1. The same part of the document is edited by several users. For example, they drive different data into one cell. A blockage occurs.
2. While using a shared file, a log of changes is kept (who logged in, when, what they did). The book is growing. Starts to "fail".
3. Removed one of the users, but so far he has not been told about it. Then the blocking can only appear on his computer.

What can be done if file sharing is blocked:

1. Clear or delete the changelog.
2. Clean up the contents of the file.
3. Cancel and then reactivate sharing.
4. Open xls workbook in OpenOffice. And again save it in xls.

It is noticed that the entry "File is locked" appears less frequently in latest versions Excel.

How to delete a user

Make sure users are done with the file before deleting it.

How to turn off sharing mode in Excel

To turn off Excel file sharing, on the Review tab, click Book Sharing and uncheck the box next to Allow multiple users to modify the file.

Only one user should remain in the list - you.