MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Make A Personal Macro Workbook

Make A Personal Macro Workbook »


May 5, 2021

Macros stored in the Personal Macro Workbook are always at your disposal. You can run the macros on any workbook that you open on the computer. Also, you do not have to jump through security hoops to run macros stored in the Personal Macro Workbook.

Adjust Xy Chart Scaling For Correct Aspect Ratio

Adjust Xy Chart Scaling For Correct Aspect Ratio »


May 3, 2021

You want to adjust an XY (scatter) chart so that both axes have the same scale per unit axis value. You want to adjust an XY (scatter) chart so that both axes have the same scale per unit axis value.

Determine The Height And Width Of The Datalabel Object

Determine The Height And Width Of The Datalabel Object »


April 30, 2021

The DataLabel object does not have either a height or width property, and Excel does not permit the user to resize a data label. In some situations, you may need to determine these properties. A case in point is a series with long, wrapping label text or labels of points that are very close to each other, where it is intended to programmatically adjust label position to get rid of any overlaps.

Create A Pivot Table From Data In Multiple Worksheets

Create A Pivot Table From Data In Multiple Worksheets »


April 28, 2021

You have more data than will fit on a single worksheet. You would like to create a pivot table from the data spread across multiple worksheets. The Multiple Consolidation feature only works when your data has a single column of text labels on the left with additional numeric columns to the right. You’d like to be able to grab similar data from multiple worksheets and summarize it in a pivot table.

Add Formulas To Smartart

Add Formulas To Smartart »


April 26, 2021

For Excel fans, the biggest disappointment with Excel 2007 SmartArt diagrams is that their text is static. You cannot have the text for a SmartArt diagram dynamically calculated by Excel.

Quickly Create Many Range Names

Quickly Create Many Range Names »


April 23, 2021

Quickly create many range names in a worksheet. If you decide that complicated formulas would benefit from referring to named ranges instead of cell addresses, you might have a daunting task of individually naming many ranges.

Quickly Create A Hyperlink Menu

Quickly Create A Hyperlink Menu »


April 21, 2021

You are building a reporting package for people who are not familiar with Excel, and you want to add a menu worksheet to help them navigate through the workbook. Usually, creating hyperlinks to another place in a document is kind of a pain, and you’d like to create a menu more quickly.

Right-drag Border To Access More Copying Options

Right-drag Border To Access More Copying Options »


April 19, 2021

You once stumbled upon a handy menu for accessing extra copying options. But how did you open it, and what can it do for you?

Copy An Exact Formula By Using Ditto Marks

Copy An Exact Formula By Using Ditto Marks »


April 16, 2021

You’ve entered total formulas in a row. Immediately below those formulas, you want to enter average formulas. However, if you copied total row cell below, the range referenced in the formula would automatically change.

Sort Subtotals

Sort Subtotals »


April 14, 2021

You want to chart the sales for the five largest customers in a data set. You can sort the collapsed view of a subtotaled data set. Here’s how:

Use Autofilter With A Pivot Table

Use Autofilter With A Pivot Table »


April 12, 2021

You’ve created a pivot table to summarize sales by customer. You now want to filter those results to show only the customers with sales between $20,000 and $30,000. The AutoFilter command is grayed out for pivot tables.

Auto-number Records And Columns In An Excel Database

Auto-number Records And Columns In An Excel Database »


April 9, 2021

You want to build formulas to automatically serially number records and column headers in a database to which AutoFilter is applied and in which selected columns are hidden.