Excel Today

2023-02-08 02:24:36 Usually when we need to create a column of numbers with a certain step, we enter the first two numbers and use the autocomplete token. However, when working with decimal numbers, the result may not be entirely accurate (there will be a deviation in the last significant digit). This can lead to errors in formulas and calculations.

To create a series of decimal numbers with the desired precision, you can use a formula that will round the value as you go. Or use the Progression tool to create an arithmetic progression. Also very convenient.

Read more about columns in the article Open / Comment

2022-01-09 19:25:18 Do you know Telegram created own cryptocurrency?

It's called Toncoin. And the blockchain is called TON. He is increasingly integrating into Telegram. Private channels with a subscription for Toncoin have already appeared.

This means that in the near future a lot of private channels with topics of interest to all of you will appear in Telegram. If you know what I mean

You can follow the development of Telegram cryptocurrency in the channel: @givemetons Open / Comment

2021-10-15 11:59:41 One often hears the question: "How to create a drop-down list for an entire row at once, and not for a separate column in a row?". Usually it is necessary to fill several columns with data at once when selecting a value from the list. Unfortunately, the drop-down list itself does not provide such an option. But the problem is easily solved by formulas.

Read more about a drop-down lists in the article Open / Comment

2021-08-14 18:48:39 If you often use sheet protection in your files, then you may have encountered such a task. You need to quickly find out in which cells of the range protection is enabled (there is a checkmark "Protected cell" on the "Protection" tab in the "Format cells" window), and in which not.

Manually checking each cell is clearly not an option. But you can use conditional formatting and the CELL function, which can check if protection is enabled. Simple, fast and clear.

Read more about the sheet protection in the article Open / Comment

2020-05-06 14:17:18

A common task is counting working hours over a period of time. If we know the time of the beginning and end of the working day and the number of days, then everything is simple. Multiply the days by the duration of the working day.

But if there is a date and time of the beginning of the calculation and the date and time of the end, then the task becomes more difficult. It is necessary on the day of commencement and on the day of termination to take into account only those hours that were working (and the counting can begin and end in the middle of the working day). Yes, and the weekend is also need considering.

Read more about the ranges in the article Open / Comment

2019-09-16 17:25:17
One often hears the question: "How to create a drop-down list immediately for an entire row, and not for a single column in a row?". Usually this is necessary to fill several columns at once with data when choosing a value from the list. Unfortunately, the drop-down list itself does not provide such an option. But the problem is easily solved by formulas.

This option is implemented by the VLOOKUP function or a combination of INDEX + SEARCH. We select one value that serves as a key (unique in the directory) from the list, and simply pull all other data into the table from the directory for the selected key.
Read more about dropdowns in article

#solo_scripts Open / Comment

2019-06-11 19:25:17
And a little more about time in Excel. Time in program is the fractional part of the day. 24 hours is one day, 12 hours - 0.5 days, 1 hour - 1/24 days, etc. Well, of course, we all know that 24 hours equals a day, 60 minutes - an hour, and 60 seconds - a minute. A simple calculation makes it clear that 1 day = 24 hours = 1440 (60x24) minutes = 86400 (24x60x60) seconds.

Using this knowledge, we can write a small formula for collecting time values from individual parts. We show a couple of options that subscribers use and share with us.
Read more about the ranges in the article. Open / Comment

2019-06-11 19:15:43
Sometimes it's necessary to highlight the cells that form the result of a formula. One way is to use the "Select a group of cells" window.

First we select the cell with the formula itself. Then we call this window (you can use the command on the ribbon, or you can press F5 or CTRL+G by calling the "Transition" window, and there you can choose "Select").

Well, in the window itself, we indicate that we need to select the "influencing cells." If you need only those links that are directly indicated in the final formula, then choose "only directly". Well, after selecting you can choose any desired fill.
Read more about the ranges in the article. Open / Comment

2019-06-11 19:05:15 In complex computational models, it is sometimes necessary to keep multiple files open at the same time. If you need some of them only for working formulas and general calculations, then you can temporarily hide them, so as not to be confused in the abundance of windows.

By the way, this setting is saved with the file. When you next open the book will be immediately hidden. But, of course, at any time it can be re-displayed in the appropriate window.
For more details on the ranges, see the article. Open / Comment

2019-06-11 19:00:17
If you need to hide zeros in a certain range of cells, then it is not necessary to resort to conditional formatting. You can use the number format. It is enough to specify how to display positive numbers, how to display negative numbers, and not to specify anything for zero values. For example, like this:

The main; -The main;

The last semicolon opens the format block of zero values. If you do not specify anything after them, the zeros will not be displayed, although they will remain in the cells.
For more details on ranges, read the article. Open / Comment