When you create formulas in Excel, you can refer to cell addresses, and you can also name cells or ranges by using the Name Manager to create the appropriate names. Can names be created automatically? Yes they can! After selecting a range, and giving a criterion in the Create from Selection option, you can create names automatically…
When we create a column chart to visualize data, the columns are often of different heights. We evaluate where the best sales were achieved visually or add data labels and then make the conclusion based on the appropriate numbers. Excel does not yet have conditional formatting for charts, but there’s a trick you can use to automatically determine the highest value in a series.
The BYROW and BYCOL functions are used to perform a calculation given by a LAMBDA function that uses only one argument for a given row or column. This, in a way, distinguishes them from the function mentioned in the previous post. Both functions work in a similar way. They differ only in whether they retrieve the data needed for the calculation from the row or column.
The catalog is a list of items associated with prices that and, by rule, it should not be changed more than once a day. In practice it is formed by consecutive adding codes of items, dates and prices in the table. How to find the last (current) price? Although at first glance it seems simple, this can be a considerable brain teaser. Also, this is an excellent demonstration of the practical application of formulas containing arrays and one of the “recipes” you will surely like.
When making extensive reports, there is often a need to calculate subtotals by reporting categories. Subtotals are, most of the time, sums and they might also be an average, a minimum, a maximum, a product, or a count of articles within a report. Subtotals can be made manually and in this „recipe“ it will be about how to use Excel for automatically adding subtotals.