One way to create a dynamic named range with a formula is to use the. Note that height is being adjusted with -2 to take into account header and title values in cells B4. The disadvantage comes from the huge size full columns and rows — care. Get over 200 Excel shortcuts for Windows and Mac in one handy PDF. Excel Formula Training. Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.
For every generation of Excel I can remember (including 2010, which I'm using now), Excel's 'Auto Size Row' features sometimes fails to actually auto size a row when the cell contains wrapped text. When it works properly, all the text is revealed and there is no additional space below the last line of text.
When it fails, it adds extra space below the text. To make matters worse, what you see is not always what you get, i.e., text that appeared okay on screen gets cut off when it's printed. You also get different sizing depending on whether you are zoomed in/out or at actual size.
Simple test case: Why is there a one-line gap after the text in cell A1 but not in A2? (To reproduce: set width of column A to 17.14 (125 pixels), text wrap on, and cell alignment top.) (I double-checked that I applied Auto Fit Row Height to both rows. Zoom level is 100%.) Is there any known remedy for this without resorting to manually adjusting the row heights (which is not practical for more than a handful of rows)? Excel's WYSIWYG isn't the best. In your picture, 'cat.'
Just barely sneaks into the 5th line. If you reduce the zoom percentage to anything less than 100% (99% for example.) then 'cat.' Is now wrapped down to the 6th line.
I think Excel is trying to auto-fit in a way that will ensure everything is almost always visible no matter your zoom level. That isn't the only problem you will have with AutoFit. In addition, the way a word-wrapped cell is printed won't always match what you see on screen.
Take your example and change the font to Courier while leaving size at 11. As you can see, cell A1 appears to be given 1.5 extra lines. Now look at print preview, 'cat.' Is completely hidden. In my experience, Excel has this problem with certain fonts and font sizes more than others. Courier is worse than Courier New, size 11 is generally worse than 10 or 12.
(Why they picked size 11 as the new default, I have no idea.). I just wrote a small macro for that purpose. I assigned it a shortcut key (Ctrl-Shift-J) and it works like a charm for me. Sub ArrangeTable 'set proper column width and row height for the current region With Selection.CurrentRegion.Columns.ColumnWidth = 200.Columns.EntireColumn.AutoFit.Rows.EntireRow.AutoFit End With End Sub To make this permanently available, you can easily make a automatically loading add-in:. create a module in a fresh blank workbook and paste the code,. assign it the shortcut key you want,. then Save As, Add-in, to folder%appdata% Microsoft Excel XLSTART This way it will be invisibly loaded every time you start Excel.
There is a on this which 'suggests' (it's unacceptably sparse, in classic Microsoft fashion) that if you ever set a row height, automatic height autofitting is permanently gone for that row of that worksheet. Rows whose height you haven't touched will autofit fine though. While you can reset column widths with the 'standard width' setting in 2003 (madman-designed 2007: change to 'default width'), but they forgot (again) (and again) to put that in for rows. So the permanency seems unavoidable. That means, if you have a default height of 15, and you create a sheet and set all the heights to 12, you just forfeited automatic autofitting. Sometimes there is the nuclear method to fix misbehaving autoproblems. What I do is copy the text out of each cell and paste the data in Notepad, then delete the row.
Then I immediately copy and insert a row that is blank but has the same formatting and good behavior as the row that was deleted. (You must copy and insert the entire row by using left row buttons.) Once that is done, you have to go back and insert the text from notepad into each cell.
(Use F2 or otherwise activate the edit mode of each cell before inserting text.) It is a pain, so it is a last resort, especially for ginormeously wide worksheets. It is the only solution sometimes, as the problem resides in the program itself. I have not thoroughly tested this but it's been working for me. Essentially it will auto adjust all sheets then go back through and set the column width to the original width, unless it's been extended by the auto adjust. I only had 10 columns and never more than 1000 rows so adjust the code as needed.
Editor's note: In the video, Brandon Vigliarolo uses and walks through the steps of building dynamic charts in Excel. The steps are very similar to the following tutorial by Susan Harkins. If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. Fortunately, the process is easy to implement in Excel 2007 and 2010 if you're willing to use the table feature.
If not, there's a more complex method. We'll explore both. SEE: (Tech Pro Research) The table method First, we'll use the table feature, available in Excel 2007 and 2010—you'll be amazed at how simple it is. The first step is to create the table. To do so, simply select the data range and do the following:.
Click the Insert tab. In the Tables group, click Table. Excel will display the selected range, which you can change.
If the table does not have headers, be sure to uncheck the My Table Has Headers option. Click OK and Excel will format the data range as a table. Any chart you build on the table will be dynamic.
To illustrate, create a quick column chart as follows:. Select the table. Click the Insert tab. In the Charts group, choose the first 2-D column chart in the Chart dropdown. Now, update the chart by adding values for March and watch the chart update automatically.
The dynamic formula method You won't always want to turn your data range into a table. Furthermore, this feature isn't available in pre-ribbon versions of Office. When either is the case, there's a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you. Using our earlier sheet, you'll need five dynamic ranges: one for each series and one for the labels. Instructions for creating the dynamic range for the labels in column A follow. Then, use these instructions to create a dynamic label for columns B through E.
To create the dynamic range for column A, do the following:. Click the Formulas tab. Click the Define Names option in the Defined Names group. Enter a name for the dynamic range, MonthLabels. Choose the current sheet. In this case, that's DynamicChart1. You can use the worksheet, if you like.
In general, it's best to limit ranges to the sheet, unless you intend to utilize them at the workbook level. Enter the following formula: =OFFSET(DynamicChart1!$A$2,0,0,COUNTA(DynamicChart1!$A:$A)). Click OK. Now, repeat the above instructions, creating a dynamic range for each series using the following range names and formulas:.
SmithSeries: =OFFSET(DynamicChart1!$B$2,0,0,COUNTA(DynamicChart1!$B:$B)-1). JonesSeries: =OFFSET(DynamicChart1!$C$2,0,0,COUNTA(DynamicChart1!$C:$C)-1). MichaelsSeries: =OFFSET(DynamicChart1!$D$2,0,0,COUNTA(DynamicChart1!$D:$D)-1). HancockSeries: =OFFSET(DynamicChart1!$E$2,0,0,COUNTA(DynamicChart1!$E:$E)-1) Notice that first range reference starts with row 2. That's because there's a row of headings in row 1. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range.
The addition of the -1 component eliminates the heading cell from the count. The first formula (for the labels in column A) doesn't have this component. It's important to remember that you must enter new data in a contiguous manner. If you skip rows or columns, this technique won't work as expected. You might be wondering why I added the Series label to each range name.
Using the name, alone, will confuse Excel. The series headings in row 1 are also names. Because the chart defaults will use the label headings in each column for each series name, you can't use those labels to name the dynamic ranges. Don't use the same labels for both your spreadsheet headings and your dynamic range names.
Next, insert a column chart, as you did before. If you enter new data, the chart won't yet reflect it. That's because the chart, by default, references a specific data range, DynamicChart1:A1:E3. We need to change that reference to the dynamic ranges we just created, as follows:. In the chart, right-click any column.
From the resulting submenu, choose Select Data. In the list on the left, select Smith and then click Edit. (Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.). In the resulting dialog, enter a reference to Smith's dynamic range in the Series Values control.
![Excel Excel](/uploads/1/2/5/3/125369540/625279799.png)
In this case, that's =DynamicChart1!SmithSeries. Repeat the above process to update the remaining series to reflect their dynamic ranges: DynamicChart1!JonesSeries; DynamicChart1!MichaelsSeries; and DynamicChart1!HancockSeries. Next, update the chart's axis labels (column A), as follows:.
In the Select Data Source dialog, click January (in the list to the right). Then, click Edit. In the resulting dialog, reference the axis label's dynamic range, DynamicChart1!MonthLabels. You don't have to update February; Excel does that for you. Now, start entering data for March and watch the chart automatically update! Just remember, you must enter data contiguously; you can't skip rows or columns. This formula method is more complex than the table method.
Be careful naming the dynamic ranges and updating the series references. It's easy to enter typos. If the chart doesn't update, check the range references. For a dynamic chart technique that takes a different route, read.