Compare Excel Worksheet Values and Formulas

If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets. (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)

For example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same.

Continue reading “Compare Excel Worksheet Values and Formulas”

Move Pivot Fields Without Dragging

Last week, someone asked me if there is a menu command for moving the fields in a pivot table layout. For example, if the Region field is in the Columns area, can you use a command to move it to the Rows area, or do you have to drag it with the mouse, in the PivotTable Field List?

Continue reading “Move Pivot Fields Without Dragging”

Excel Drop Down List Allows Invalid Entries

Drop down lists make it easier to enter data on a worksheet, and they help ensure that only valid items will be entered in the cell.

For example, in the screen shot below, the Manager column has a drop down list with 5 names. Only those names should be entered in the column.

Continue reading “Excel Drop Down List Allows Invalid Entries”

Counting in Excel

The most popular function in Excel is SUM, based on my experience. That’s probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.

Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say “en-count-er”?):

  • Sales: number of orders for a specific product, from a list in a worksheet
  • Human Resources: count of employees with 10+ years employment
  • Shipping: number of shipments pending, that do not have a zip code

Fortunately, your favourite spreadsheet can help – there are many different ways to count things in Excel.

Continue reading “Counting in Excel”