Excel Today

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

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

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

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

Content from channel Excel Everyday 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

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.

Content from channel Excel Everyday 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.

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.

Content from channel Excel Everyday 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.

Content from channel Excel Everyday Sometimes it's necessary to find out which named ranges are used in the file and what they refer to. This is especially true when you working with another's calculated files. You can get a list of all names with the "All names" command. It is in the Insert Name window. Well, the window itself can be opened either by a command on the tape, or by pressing F3. Before applying the command, make sure that there is enough empty space next to the active cell, since the list will be inserted relative to it. For more details on ranges, read the article.