Dynamic table with Autofilter & Subtotal

We often need to take out those un-wanted rows but we don’wan’t to delete them.
Today we will discuss two really simple things Autofilter and Subtotal

Take the sample data file from previous post

http://appliedexcel.wordpress.com/2008/07/24/

Apply Autofilter as follows

Dynamic table with Autofilter & Subtotal : Autofilter Example

Insert one row above the table

Dynamic table with Autofilter & Subtotal : Insert one row above the table

In Cell E1 type the Subtotal formula, remember we want to sum those values hence use option ‘9’

Dynamic table with Autofilter & Subtotal : Add Subtotal

Copy the formula for Cost & Agent Fee and we are already there!

Dynamic table with Autofilter & Subtotal : Finish

Try changing the Autofilter category, you’ll see that the Total will change consequently to the filtered table.

Find and Replace cells’ formula – increasing efficiency

Most of my friends are quite capable of doing some basic formula with Excel / OpenOfficeCalc, but when it comes to routine works that require same formulas to process daily/weekly/monthly whatsoever data, they tend to re-create the formula again and again instead re-use/re-cycle the formulas.

Well it’s time for some efficiency example.

Suppose we are in some company that sells their products through branches and agents, we have a task to recapitulate weekly data, in this example those files are data-week1.xls and data-week2.xls
as shown below

data-week1.xls

data-week2.xls

We need to have a recapitulation of Sales, Cost, and Agent Fee
Create a new file to recapitulate data in those two files, here we choose the SUMIF syntax for this task
Lets name it 02_summary-weeks.xls

02_summary-weeks.xls : Recap Total Cost

We just recapitulated the Total Cost for Week 1, yes we can do the similar things to Sales & Agent Fee
But instead of doing the rather lengthy way, lets take a shortcut.

• Copy the formula from B4:B11 to C4:C11

02_summary-weeks.xls : Copy Formula from Cost to Sales

Notice that the formula still referring to the same cells ($E$2:$E$101) the Sales columns in data-week1.xls, we need those $E switched to $F

• Block cells range C4:C11 then do a Find and Replace, Find $E$ replace with $F$, the Total Sales will be created

02_summary-weeks.xls : Total Sales Find and Replace

Do similar things to Total Agent Fee, and we’ll have Week-1 recapitulaztion report.

How about week 2 ? not so much different

1. Copy cells range B4:D11 to E4:G11
2. Both files (data-week1.xls & data-week2.xls) must be opened
3. Block cells range E4:G11 then do a Find and Replace again , Find data-week1.xls replace with data-week2.xls

02_summary-weeks.xls : Week 2 - Block Cells

Voila Week2 recapitulation is done

02_summary-weeks.xls : Recapitulation Done

You can do a lot with Find and Replace formulas, the application depends on your creativity
Get the above example files here

Cell Options / Validation for Budgeting or Forecasting

Have you ever been working on your budgeting or forecasting task, which most of it has more than one options/assumtions.
Yes if you use spreadsheet, changing cells already help you a lot, but ever considering a more convenient way and still looking for it ?

Well here is your answer, in Microsoft Excel it’s called Validation, in OpenofficeCalc we call it Validity.

Option example

By combining with some simple VLOOKUP or HLOOKUP syntax will bring you efficiency just like a snap.

Budgeting / Forecasting example

Step 1: After creating your assumption, create the validation / validity

Menu -> Data -> Validation

Step 1

Step 2: Change ‘Any Value’ to ‘List’

Change 'Any Value' to 'List'

Step 3a: Fill your options

Step 3a: Fill you options

Step 3b: or alternatively choose your options

or alternatively choose your options

Step 4: Link your options with VLOOKUP

Link your options with VLOOKUP

Well that’s all, need some improvisation for a better appreance

Download this file for above example

Good luck!