Power in PivotTables

This past week I sent out a message letting you know that right now I am accepting students into my PivotTables for the Faint of Heart course. If you register for the course you can discover in a hands-on way how to use PivotTables, slicers, timelines, and PivotCharts to help analyze your data in ways you never knew you could.

This course is jam-packed with information you can put to use right away. Full information can be found here:

     https://usingoffice.com/pivottables/

Plus, right now (through this coming Wednesday) you can register for the class at 25% off the regular price. So, through this coming Wednesday you can register for as little as $74. You won't find a better deal on high-quality training anywhere. The PivotTables for the Faint of Heart course is a great and economical way to expand your use of Excel. I hope you'll take the chance to check it out.

—Allen
     

ExcelTips (ribbon) for 6 April 2024

Macros extend Excel
Stopping Fonts from Changing

There are multiple ways that Excel can create a workbook, and the formatting in each creation method can be different. In this tip you discover how to figure out what creation method a macro is using so you can track down the source of formatting in the new workbook.

Read this tip »

(Thanks to Ryszard Raciborski and Cedric McKeever for contributing to this tip.)

 
Date formulas
Determining Contract Weeks

Everyone seems to determine the difference between dates differently. Nicole has a need to calculate contact weeks (the number of weeks between two dates) in a way specific to her company. This tip examines how this can be done.

Read this tip »

 
Baffled by Array Formulas?

Have you heard about the special array formulas you can create in Excel? They make it a snap to perform calculations using large amounts of data and even generate results you can't get with regular formulas. Array formulas may be Excel's best-kept secret. Remove the confusion; discover how to use array formulas today.

 
Workbooks
Opening a Workbook as Read-Only

When you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This is where opening a workbook as read-only comes in handy.

Read this tip »

 
Editing
Repeating a Pattern when Copying or Filling Cells

The fill tool can be a great help in copying patterns of information in a column. It isn't so great, though, when the pattern isn't easy for Excel to discern. This tip shows some ideas on how you can fill a range with one of those types of patterns.

Read this tip »

 
Formulas
Summing Absolute Values

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a range. Here is a full discussion of the various ways you can get the desired sum.

Read this tip »

 
Macros extend Excel
Pulling Cell Names into VBA

Excel allows you to define names that can refer either to ranges of cells or to constant information, such as formulas. If you have quite a few names in a workbook, you may want to derive a list of those names. The macro in this tip allows you to pull them all and place them in a worksheet where you can continue to work with them.

Read this tip »

     

Help Wanted

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

 
Locking Graphic Annotations to Chart Data Points

I have several charts of daily data, and each day I add more data. On a chart of I need to make annotations, like arrows or lines referencing certain data points. As new data gets added, the plot shifts to the left in the chart, but the arrows/lines stay in the same place. Thus, they no longer point to where they should. Is there a way to lock the annotation to the actual data point it references?
—Tom Weaver (provide an answer for this Help Wanted question)

 
ExcelTips is a free service of Sharon Parq Associates. You can find thousands of tips at our website and a whole lot more. ExcelTips is part of the Tips.Net network.
Sharon Parq Associates, Inc.  •  PO Box 1187  •  Mountain View, WY  82939  •  307-200-0450