As part of our Speedy Spreadsheet Week, I have asked you to share your favorite tips & techniques for speeding up Excel. And what-a-mind-blowing response you gave. 75 of you responded with lots of valuable tips & ideas to speed-up Excel formulas, VBA & Everything else.
How to read this post?
Since this is a very large article, I suggest reading few tips at a time & practicing them. Consider bookmarking this page so that you can refer to these wonderful ideas when you are wrestling with a sluggish workbook.
Thanks to all the contributors
Many thanks to everyone who shared their tips & ideas with us. If you like the tips, please say thanks to the contributor.
Please note that I am not able to share some of the files you emailed as they contained personal / sensitive data.
Read Excel Speeding-up tips by area
This page is broken in to 3 parts, click on any link to access those tips.
Formula Speeding-up Tips
VBA / Macros Optimization Tips
Everything Else
Share your tips
Formula Speeding-up Tips
I use Formula-Calculation Options-Manual to disable calculations when setting up complex inter-relating formula pages. This stops Excel from churning through calculations every time I change a cell, saving my time. I just hit F9 to recalculate when I want to see the results.
I use
Application.ScreenUpdating = False and
Application.Calculation = xlManual
to speed up macros, and
Application.StatusBar = LoopNum
so I can see the status of my macro and estimate how long there is left to calculate. Don’t forget to switch these back at the end of the macro!
When I have complex formulas with results that won’t change, I hard-code these to save calculation time, but I keep the formula only in the first cell, or pasted in a comment.
Hi Chandoo,
In spreadsheets that have vlookups, if the source file is not going to change; I have realized that it is better to paste-special the vlookup values. This is because even a couple of vlookup slows down the file massively on account of recalculating of values.
Another step I take (this depends on the criticality of data and other factors) is to set the auto-save function in excel to an infrequent duration.
Adi
Replace sum products with count ifs or sum ifs where possible – they calculate a million times faster!!
Avoid large number of SUMIFS, instead, aggregate data into a PivotTalble, then use the Index(Match) combo to locate the sums.
I have dramatically sped up large worksheets doing this.
1. Change Calculation to Manual mode. Calculate manually only when required.
2. Delete all name ranges, unused area, unnecessary formatting.
I think some of the more basic, but highly effective tips to speed up larger workbooks are:
1.) Avoid array formulae, where possible. Everyone knows there are a million ways to skin the proverbial Excel cat. Find alternatives to array.
2.) Adjust the calculation options, if necessary. Frequent calculations = sluggishness. A word of warning, though – people need to know if calculations aren’t automatic, or it can/will cause confusion.
3.) If all else fails, copy and paste as value. If the recipients of your data don’t need the flexibility to enter new data and update values with calculations, take formulae out of the equation (no pun intended) all together.
I replaced all my SUMPRODUCT formulas with SUMIFS and calculation time went from about 50sec to instantaneous. My system is a AMD 6 processor with 8gig memory, Excel 2007.
Stay away from array formulas (unless to have calculations on Manual).
A simple, little tip/trick for speeding up calculating:
Sometimes in a workbook you have so many formulas that for effective work you have no other choice but to turn off the auto-calculating. Still you work on your workbook, writing new formulas, there is no problem if you just wrote one formula in one cell – it can be calculated by just F2-Enter combo. Problem is when you created a new formula for a whole column – you don’t have to calculate whole workbook now or “F2-enter” every cell – just select the column you want to calculate, Ctrl+H and change “=” for … “=”. It’s a known trick, still some people may not know it yet. Cheers.
PS. I don’t work on English version of Excel so my translations may not be accurate.
I use templates with formulas in them that I add data to every month, and once I paste the next months data I copy down the formulas recalculate and then copy and paste the formulas except for one line of formulas for next month. In this way my spreadsheet of 200-300k & growing lines doesnt have to recalculate all the rows everytime.
I set the Automatic Calculation option to manual make any changes in Excel and then hit F9 to calculate as and when required or set back to Automatic once I have completed any large or slow spreadsheets. Save me so much time and frustration. I would love to hear any other tips on speeding up spreadsheets.
Cut down on the use of Array formulas – particularly if they are nested in IF statements.
Speed tips for formulae
1 As you type formula after the =sign, when the prompt appears select the down arrow key and press Tab key so that the function is inserted. Then press the fx in the formula bar to bring up the prompts and start filling the blanks
2 Use f4 key for referencing
3 When using the Rept formula use “l” which is L in small caps and then type the number of times you want to rept.
4 can combine 2 rept commands by shrinking the column width than doing long formulae
5 Rept formula is a powerful tool and can used to show both negative and positive values For e.g. profit and loss A/C can be shown in rept formula
another use of rept formula is to use it for confidence interval with mean in the middle.
6 To make Vlook up to look up values in the right side: copy and paste the columns next to each other and perform vlook up. it is easy and there is no need for another formula For eg;Name and Phone number in two columns
Vlook up will look up the name and will return the phone number. If we have phone number and want the name then we need to write a match and index, Instead if you copy name and phone next to each other then for the phone number vlook up will return the name. That is easy.
I am feeling sleepy after this. More later
Nice subject!
My 2 cts.
A. Formulas
1. If you need to turn off recalc, it is time for a redesign.
2. Avoid array formulas (this includes sumproduct), instead use helper columns which have intermediate results. Easier to debug and very often much faster
3. Avoid VLOOKUP, especially on large tables, instead, use INDEX combined with MATCH, where you use a helper column for the match so you only ask Excel to search your table once for each row instead of once for each column in a row.
4. Do your summarizing with Pivot tables instead of functions
5. Be prudent with range names. Use them sparingly and limit them to constants. Formulas with range names are harder to audit because of the extra layer between your formula and the grid.
6. Visit www.decisionmodels.com, the site contains a wealth of information on recalculation in Excel.
I work with files that use a lot of data tables. In order to avoid excessive delays I will turn off the automatic setting under calculation options and select automatic except for data tables. In addition, I have noticed that excessive conditional formatting can really bog down the spreadsheet as well. Thus, I try to limit and consolidate formatting needs where I can.
Use as many array formulas as possible on the staging worksheet. That way the Excel or UDF functions are called as few times as possible.
The way I speed up my workbooks is by pasting values (instead of keeping the formulas) once the data is no longer going to be updated. For example, I have files that track activity that has happened each quarter. The sheets often have 35,000 rows of data and formulas in each of the 10 columns (for each row). As soon as the quarter is over, I paste the values over the formulas since things won’t be changing any longer.
Perform paste down macros for all calculations. These use dynamic named ranges to select a row of formulas, then paste them in against a table of data. This way you can calculate formulas against thousands of rows and then copy-paste special with values. Removing live formulas seriously reduces calculations times for workbooks with 1K+ rows of data.
Perform Sorts and then use range formula (OFFSET, INDEX) to select a subset of rows, rather than using conditional formula on whole columns. SUMIF, COUNTIF, array formulas etc are very slow on big columns of data. Sorting can filter a table to records that share the same attribute and range formulas can pick up row numbers to only select a sorted block of values.
Keep use of array formulas to a minimum. Keep calculations running sequentially from top left to bottom right when possible. Break up larger internal formula calculations into smaller bites (more columns etc). Look for formula parts shared by formulas. Use offset to keep lookup formulae to the minimum required ranges. Use built-in formulas whenever possible.
I work with large workbooks with extensive formula throughout. I used to use VBA to paste in formulas then I would value them out, but my clients couldn’t easily modify the formulas if they desired a change. Since then I would place a formula row at the top of the data and use VBA to copy that row and paste formulas below, calculate then value them out. The client can then modify the initial row of data to suit their needs. This greatly improved save and load times.
When I have thousands of rows of equations (all the same), I convert all but the top row to values. Then I create a macro that spreads the equations from the top row down to all the necessary rows and makes them values again. Saves a lot of excel recalculating.
use iferror instead of if(iserror(…
I have Excel 2003 files of 45 Mb plus that track daily shift performance that have lots of vlookups, conditional formats, data validation, event triggered VBA. To speed things up I cheat! The historic data is copy-special pasted over itself to turn it into values only – so when auto updates happen they only process the “current data”.
One thing I do where there are multiple columns with formulas is this:
Once my formulas have all calculated and I know the the results won’t change, I copy the formula and put it at the top of my spreadsheet. Then put a red top & bottom border around the formula so I can easily find them.
I then copy the data set full of formulas and re-paste it on itself (keyboard shortcut – copy/file/paste special/values). The spreadsheet calculates much faster.
When I need to update the data I just copy the row of formulas and paste them over the data rows.
save as .xlsb to speed up opening time/decrease file size. also changed =if(iserror) to =iferror to speed up processing. changed from vlookups to pivot table/=getpivotdata format to speed up processing
a) Delete/or clear contents on all blank cells under & to the right of my data. b) On old inherited files, clear out old range names. c) use specific cell references for vlookups (rather than entire columns) d) remove as many calc’s as possible (copy-paste-special values) e) keep pivot tables in separate file from data file f)Stopped using arrays & sumproduct() completely 🙁 g) now considering upgrading to 64 bit OS & 64 bit MSOffice 2010 (currently using 32 bit MSExcel 2010 on XP)
1. Define name of ranges and Use it in the Formula if data is flowing from database.
2. Remove the unused name or name resulted any error or scope outside the workbook.( Formulas—>name manager)
Reduce Images / Shapes that reduces the performance
Linking all my dashboards with pivot tables and queries for to update complex data with one click.
My array formulas used to reference an entire row or column (e.g. A:A or 1:1), and I’m pretty sure that slowed down the sheet. I shrunk the reference to go through, say, row 5000, and it appears to have helped the problem.
No doubt excel is a powerful analytical tool but most of the people do not plan before designing there spreadsheet. One should plan the Start and End in mind, and the assumption that the spreadsheet will never be used again should kept out of mind. Perhaps this is might be the number one rule. Spreadsheets are about giving correct information to the user, not possible erroneous information that looks good.
Excel Best Practices & Design
Formatting
Your spreadsheet should be easy to read and follow. Most of the users spend about 30%, or more, of their time formatting their spreadsheets. Use the cell format of Text if really necessary. Any cell containing a formula, that is referencing a Text formatted cell, will also become formatted as Text. This format is not usually needed but very much used. If you apply a number format to specific cells avoid applying the format to the entire column. If you do, Excel will assume you are using these cells.
Layout
Try and ensure all related raw data is on one Worksheet and in one workbook. When putting in headings bold the font. This will help Excel recognize them as headings when you use one of its functions. When putting data into the data area of your spreadsheet try to avoid blank rows and columns. This is because a lot of Excels built-in features will assume a blank row or column is the end of your data. Use real dates for headings and format them appropriately. If you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as “mmmm”. This is a very simple procedure that is all too often overlooked by many. Don’t put in one cell what could go in more than one cell, i.e. the names of 100 people to put into your spreadsheet, don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right.
Formulas
This is the biggest part of any spreadsheet! Without them you really only have a document. Excel has over 300 built in Functions (with all add-ins installed), but chances are you will only use a handful of these.
The usual practice in regards to formulae in Excel is the referencing of entire columns, this is a big mistake! This forces Excel to look through potentially millions, of cells which it need not be concerned with at all. One of the very best ways to overcome this is to familiarize you with the use of dynamic named ranges.
Speeding up Re-calculations
A common problem with poorly designed spreadsheets is that they become painfully slow in recalculating. Some people will suggest that a solution to this problem is putting a calculation into Manual via Tools>Options>Calculations. A spreadsheet is all about formulas and calculations and the results that they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information off your spreadsheet which will not have been updated, this means using F9 on regular intervals, which can cause bad results, because Pressing F9 can be overlooked.
Arrays, Sumproduct (used for multiple condition, summing or counting), UDFs, Volatile Functions and Lookup functions, can slow down the recalculations of spreadsheet.
Array Formulas
The biggest problem with array formulas is that they look efficient. An Array must loop through each and every cell they reference (one at a time) and check them off against a criteria. Arrays are best suited to being used on single cells or referencing only small ranges. A possible alternative are the Database functions. Another very good alternative which is mostly overlooked is the Pivot tables. Pivot Tables can be frightening at the first site but it is the most powerful feature of Excel.
UDF (User Defined Functions)
These are written in VBA and can be used the same way as built in functions can be, but unfortunately, no matter how good the UDF is written the, it will perform at the same speed as one of Excel’s built-in functions, even if it would be necessary to use several nested functions to get the same result. UDFs should only be used if an Excel function is not available
Volatile Functions.
Volatile functions are simple functions that will recalculate each time a change of data occurs in any cell on any worksheet. Most functions which are non-Volatile will only recalculate if a cell that they are referencing has changed. Some of the volatile functions are NOW(), TODAY(), OFFSET(), CELL(),INDIRECT(), ROWS(), COLUMNS() . If you are using the result of these functions frequently throughout your spreadsheet, avoid nesting these functions within other functions to get the desired result especially in array formulas and UDF’s. Simply use the volatile function into a single cell on your spreadsheet and reference that cell from within other functions.
Lookup Functions
The Famous Vlookup(). Excel is very rich in lookup functions. These functions can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously. The other mistake is that the lookup functions are told to find an exact match. This means that Excel will need to check all cells until it finds an exact match. If possible, always use True for VLOOKUP and HLOOKUP. So, whenever possible, sort your data appropriately. Sorting the lookup columns is the single best way to speed up lookup functions. Another Bad practice is the double use of the Lookup Function nested within one of Excels Information functions. Like =if(isna(vlookup(cell ref,Range,2,false))=true, “Please check”, (vlookup(cell ref,Range,2,false)))
This is used to prevent the #N/A error from displaying when no match can be found. This forces Excel to use the VLOOKUP twice. As you can imagine, this doubles the number of Lookup functions used. The best approach is to live with the #N/A, or hide it via CONDITIONAL FORMATTING.
LAST WORDS
Lean to us e database functions. They are very easy to use and are often much faster than their Lookup & Reference counterpart.
Microsoft Tips
Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
By changing formulas to manual from automatic
If my model has lot of formulas in the data sheets and working on the summary tab – then I will Keep my formula calculation option as “Manual”.
If you are doing calculation in one sheet Pls use Shift+F9 (to get refresh the formula in the active sheet).
F9 – to refresh the complete workbook.
1. arrange source data before linking to dashboard / report with macros and other aggregate functions
2. separate results into several charts & link list boxes to just one calculation
3. avoid volatile and array functions
VBA / Macros Optimization Tips
First. I find your site awesome.
Well I speedup my VBA code by setting
Application.ScreenUpdating to false
Application.EnableEvents to false and
Application.Calculation to xlCalculateManual
and then setting those values back to whatever they were before I made the changes. I do EnableEvents when I use a Event Driven actions and I know that I do not need them during those calculation/operations.
Two approaches.
a) Profile both worksheet calculations, and if necessary VBA code using the profilers downloadable here to identify and report on slower performing calculations and code.
http://ramblings.mcpher.com/Home/excelquirks/optimizationlink
b) in VBA, always abstract data from the worksheet and work on the abstracted object model.
http://ramblings.mcpher.com/Home/excelquirks/classeslink/data-manipulation-classes/howtocdataset
We design macros which we run across the many worksheets. If formulas are generated, we do final macros to save the formula results as numbers. This retains our worksheets as light.
Regards
David
1. Disable screen updating in VBA.
2. Set calculation to Manual, use Shift-F9 to calculate each sheet as needed. Is a pain, but I have found it is a major time saver on a couple of my largest files.
Recently, I’ve been busy with a project to emulate software for seeking secret messages in classical texts using EXCEL. I need to write hundreds of thousands of single letters each in a cell, and I’ve found it faster to operate internally VBA and finally write as a block in a declared range, rather than doing it via a loop writing individually each cell.
I haven’t measured times, but I would venture it’s a lot faster.
Not sure if this is what you are looking for – but here is what I do to speed up my excel workbook –
Along with all standard keyboard shortcuts – I have been creating a lot of Macros. I ran out of shortcut keys I can use with Ctrl – so now started using Ctrl+Shift to create my own shortcuts. (May be I don’t know any existing shortcut- and tried to reinvent the wheel for some of them)
I have Macros for – Green/Yellow/Pink Highlight – Merge + Wrap Text – Enter TB Link (Entering specific formula to cell) – Single Underline Cell
Just thought to share this as you asked for – considering all the entries I have seen from others on your website, I am just a newbie in the Excel World.
VBA: One powerful one is to use “Destination:” in your copying and pasting which bypasses the clipboard. Or if only values are wanted simply assign values.
So instead of:
Sheet1.Range(“A1:A100”).Copy
Sheet2.Range(“B1”).pasteSpecial
Application.CutCopyMode=False
‘Use:
Sheet1.Range(“A1:A100”).Copy Destination:=Sheet2.Range(“B1”)
If values only required ditch copy and simply assign values from one place to another:
Sheet2.Range(“B1:B200”).Value= Sheet1.Range(“A1:A100”).Value
Avoid loops like the plague while writing macros, unless absolutely necessary.
A tip which is well documented when searching for ways to improve performance when using VBA/Macros is to turn off screen updating, calculations and setting PivotTables to manual update.
Most of the procedures I create in VBA start with:
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
And will end with the following statements:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
If PivotTables are involved then I include the following in procedure:
With PivotTable
.ManualUpdate = True
End With
And will end with the following statement
With PivotTable
.ManualUpdate = False
End With
Planning carefully before coding.
Passing the entire SQL query into the code, leaving no connection on sheets.
This is a very general tip, but when using VBA — AVOID LOOPS!
Use the “Find” and “Search” methods rather than looping through cells. Loops work quick when you are using less than 100, or sometimes less than 1000 cells — but start adding more and you will be in for a waiting game.
If you have VBA code that writes updates to the screen, this slows down the code (I/O is slow). If you have a lot of screen writes, the code can be painfully slow. You can turn off screen writes while your code is running and then do one massive screen write at the end of the macro. Up at the beginning of your code, maybe just after you declare variables, add the line “Application.ScreenUpdating = False”. At the end of your code, you need to turn screen writes back on so add the line “Application.ScreenUpdating = True” just before you exit the macro.
If you have a load of screen writes, the speed difference can be dramatic.
Hey Chandoo
VBA-speed
at the beginning of the macro
…
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
at the end of the macro
….
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
end sub
regards Stef@n
With Application.ScreenUpdating = False / True
With Application.Calculation = xl.CalculationManual
Using the statement with wherever is possible
and release memory when the objects variable are not used anymore
Everything Else
1. I list out the things required and will imagine the plan of my task.
2. I try to minimize the calculation for speedy calculation. So, I am trying to learn new formulas.
3. In each and every step, I consider about the others who use that excel. So that I can make the workbook user-friendly to others also.
I close MS Outlook when working on heavy files. Basically I exit all the programs that will eat into process speed. It helps to an extent.
Also, I try and minimize cross linking of files.
Power Pivot from Microsoft. This looks like it would solve the problem of large amounts of data.
http://office.microsoft.com/en-us/excel/powerpivot-for-microsoft-excel-2010-FX101961857.aspx
I wrote a blog article on my favorite tips here: http://www.plumsolutions.com.au/articles/excel-model-file-size-getting-out-hand
I haven’t done this myself, but a consultant we used sped up our dashboard by writing VBA code which “dumped” a lot of the back data after it was loaded. This greatly reduced the amount of data stored, thus reducing file size, thus sped up the dashboard.
1. Many database download from whatever system may include blank data occupying cells from the last row of data to the last possible row Excel can provide. So I would look at the data set and delete those rows (or columns, but I see more blank rows than blank columns).
2. Too many pivot tables: I’d ask the person who create multiple pivot table on the same workbook to see if there is a need to maintain those pivot tables. If the answer is
a) no need to maintain: I’d delete.
b) need to maintain but may not be in a pivot table. I’d convert pivot tables into just text/data (thereby removing the pivot function) table.
3. Try to reduce the number of worksheet. I found out that the size of a workbook (I can’t prove it but it’s my general observation) would expand if there are more worksheets.
Standardize, standardize, standardize! The more you are “boring” the quicker it will be to set up work with (and have others) use your formats!
Items to standardize:
– Font
– Color scheme
– Headers
-Colors for “input” (font color or fill color)
-tab color scheme (answers, data input, analytics)
– Color for “answer/solution”
– Lead with a recap page (easy and quick to find the solution)
-Configure to print (courtesy to others – if you need to print an answer – set up the parameters before sharing)
by using single sheet in work book & using alt,clt short key
Oddly enough, the best thing I have found to speed up Excel is to completely disconnect internet access for my computer. I don’t know why, but Excel is unbelievably slow when I am otherwise online, and speeds up immediately when I disconnect. I’d love it if someone could help me understand why this is the case.
Even though I’ve been using Excel for quite some time, I learn and love your site. You teach me the impossible. The simplest way I at least save data space is to save it in .xlsb format. I read somewhere that even a .xlsx is basically a number of zipped or compressed files that need to open and save. Not sure about that, but know the binary file is much smaller in size than the others. Not sure if macro enabled workbooks will save as binary. Thanks. Always look forward to what the next email will hold…scary sometimes. -Jim
I’m not an expert but try to keep the dashboard as much simple as possible.
I use access to have the main table and from that table we create different dashboards and reports and pivots to analyze data
I break any links to the spreadsheet that I am not using.
Try to avoid adding formatting over an area larger than you need, I’ve found that if you format a whole row, column or worksheet it can slow the workbook down and create large files
Dumping out as much unnecessary data as I possibly can, converting formulas to values whenever possible and making sure the empty space on each sheet is empty.
Also I’m using lots of pivot tables on my spreadsheets so I’m trying to use as few pivot caches as possible and trying to use external data sources for my PTs whenever possible (or deleting the original data once the PT is created).
Most of the time, to increase speed & size. what I do is
1) simply copy the used data cells to a new sheet, (by selecting from A1 to the end of the data cell),
2) if there are too much of borders decorated around more cells, then try replacing these borders with minimum dotted lines (just to highlight the difference)
3) Avoid using too many fonts in the sheet
4) Cut short complicated formulas or multiple linked formulas,
I have Liked based models. I try to make my links as small as possible. I try to put all the sheets in one file and interlink them so that they take less storage space and react much speedy in working.
Create a view in SQL and set a scheduled task to run to generate the view before you update the dashboard.
Do we get some SWAG for sharing??
Here are some tips I’ve collected, although they repeat some points they provide different viewpoints:
10 WAYS TO IMPROVE EXCEL PERFORMANCE
http://www.techrepublic.com/blog/10things/10-ways-to-improve-excel-performance/2842?tag=nl.e072
EXCEL 2010 PERFORMANCE: IMPROVING CALCULATION PERFORMANCE
http://msdn.microsoft.com/library/ff700515.aspx
CLEAN UP YOUR MACRO LIST
http://excelribbon.tips.net/T008037_Clean_Up_Your_Macro_List.html
OPTIMIZE MACROS – VBA CODE CLEANER
http://www.appspro.com/Utilities/CodeCleaner.htm
I recently happened to work on a report which has 1.5 lac rows of data in 16 columns. The requirement was that in the main report as soon as a change is made say for a dept or month the numbers should accordingly change. I tried most known formulas like Sumifs, Sumproduct, Vlookup, Index and Match. How ever the calculation time these formulae took was much more compared to one formulae that I felt was the fastest in terms of calculation. That was Getpivot data.
I basically used the “show all report filter” option in the Pivot options to generate summary data in around 500 tabs using the my base data. Then I used get pivot data formula in my report file. Though the file size was a bit huge still my formula get calculated almost instantaneously.
Also one strange thing I noticed in one other file of mine was that when I press Ctrl+end the last cell it stopped was in some 2 lac row or something, how ever the data was only in some 10k rows. I used clear all option from the last cell from where I have data to the last cell it went when I pressed Ctrl+end .By doing this my file size came down from 12 MB to some 600kb.. 🙂
Hope this helps someone.
Hi,
Great topic (as usual).
One thing I like to do is minimize links between workbooks. Instead of using live links to import data I like to use import and export sheets. These are identical sheets on the origin workbook (for export) and the receiving workbook (for import). Values are calculated in the origin workbook and pasted to the receiver as values only.
This gets rid of messy links and keeps spreadsheets smaller and tidier.
One thing to be careful of is that if one changes the other has to change so they stay identical.
Thank you again for your excellent material.
That’s my problem too I would love to hear what others say. For
me closing other spread sheets and unnecessary opened tasks in your PC helps.
– remove external data links – better to import a large data table – or use an SQL statement if possible.
– especially don’t use INDIRECT to anything external
1) Limit color use in Excel
2) Hide gridlines (with “View Gridlines” function) rather than color the cells white
3) Create smart Vlookup formulas (Arrange data in the lookup tab so the range is as small as possible – 3 columns vs. 20 columns)
4) Link multiple tabs using the same data to one data tab. Ie.. Dates, headers, etc…the links will eliminate having to update each tab.
5) Extract only the needed data from the database (10 columns of needed data vs. 40 columns available data.
6) If the Database report does not allow the user to choose what is exported, export the data, organized the needed data into a consolidated area (rows x columns), Copy & paste into a new tab and delete the original tab. Many people leave the original data in the file, which slows down the file and adds to the file size.
1) Don’t create different pivots from same data. Copy the old and slice into the new one.
2) Go to special –> Check Last known cell and delete unnecessary data and formulae.
3) Set Calculation option to manual. Do all the dirty work and finally make it automatic and go for a coffee break 🙂
4) Use excel tables as far as possible.
To speed up workbooks, the reduction of formulas within the workbooks should be the main aim.
If the data is being pulled from an external database into the worksheet:
– Do not start calculations on that data in the workbook which could have been done before, e.g. using formulas to split up the dates like mm-dd-yyyy, weekdays a.s.o..
– Reduce the amount of sources if possible and combine the data in one sheet
– Use name ranges
– Split up the workbooks for different purposes (Dashboard for CEO, Dashboard for CFO a.s.o..).
– Try to use only one format for importing (I prefer *.csv or *.txt)
– If you connect your Workbook directly to a SQL database, make sure the connection is high-speed
If the modeling is too complex, think of using a a (semi-) professional data ETL tool in between or use the additional add-ons like PALO or Pentaho available as open source to rise the power of multidimensional databases for your BI-tools.
This can and will save time in calculating for the necessary functions of the workbooks. Stay with KISS (Keep it simple, stupid).
I use name range for multiple pivots, basically the offset function, this not only speeds up my calculation but also reduces the size of the workbook.
More on Excel Optimization & Speeding up:
Read these articles too,
- Optimization & Speeding-up Tips for Excel Formulas
- Charting & Formatting Tips to Optimize & Speed up Excel
- Optimization Tips & Techniques for Excel VBA & Macros
- Excel Optimization tips submitted by Experts
- Excel Optimization tips submitted by our readers
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. |
VBA ClassesLearn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff. |
How do you speed-up Excel? Share your tips
Between these 75 ideas & and previously written articles, we have covered a lot of optimization & speeding-up techniques. What are your favorite methods? How do you optimize & deal with sluggish workbooks? Please share your ideas & tips with us using comments.
245 Responses to “Creating KPI Dashboards in Microsoft Excel [Part 1 of 6]”
Pretty cool!
Thanks!
Nice Post !!!
Expecting your next article!
@DPizz and robert:
Thanks for taking the time to read the post and for your comments. We will add more interesting dashboard techniques in the next posts. Stay tuned!
What a shame Maximum Value can't be set as a formula so it would dynamically count the no of entries in your source data.
I guess you could set it to 999 and then use conditional formatting to hide the ugly zero entries. Still means users will see scroll through blank rows at the end of the data but maybe I could live with that.
Great tip though.
@Adaptive Dash... thanks and welcome to PHD. Indeed the maximum count can be set as a formula in the calculation tab. We can try to do so in the next part of the tutorial. Thanks for asking 🙂
Very nice. I Hope more tips. Thanks!
@AdaptiveDervish: Thanks for the comment!
Yes you are right: you can’t link the maximum value of a forms scroll bar from to a cell reference. You would have to change it manually every time the number of data rows changes.
One possible solution could be the idea you described. But I think this wouldn’t be convenient and user-friendly enough. The user would scroll down to empty parts of the list without knowing where the real list ends and he would not be able get to the end of the real list quickly.
Probably the better way would be to use a small vba routine to set the maximum of the scroll bar. Go to the vba editor and copy the following code to the sheet “Dashboard”:
Private Sub Worksheet_Activate()
ActiveSheet.Shapes ("ScrollBar Liste").ControlFormat.Max =Sheets("Calculation").Range("$D$6").Value
End Sub
Whenever the dashboard is activated, vba will change the maximum of the scroll bar according to the value calculated in cell calculation!D6 (=number of data rows minus number of items displayed on dashboard +1).
The formula for calculating the maximum is already in the version posted for download. The result of the formula was used to control one of the two small triangles on top and at the bottom of the scroll bar. I haven’t mentioned the triangles in the post because they are not important and not really necessary for the technique. All they are doing is giving the user additional information whether he has reached the top or the bottom of the scroll bar.
So: you already have the formula to calculate the maximum value; all you have to do is copying the procedure (see above) into the vba-editor.
We haven’t included that routine intentionally. We tried to show some useful dashboard techniques without vba.
@AdaptiveDash: I incorrectly understood your comment, read Roberts follow up to understand the limitation in using scroll bar controls.
as Robert pointed, the intention is to keep VBA out so that this technique can be used with ease. If you know some unconventional way of getting around this hitch let us know... as usual a donut awaits you 😀
@AdaptiveDervish:
Here is another work-around for your request without vba:
1. Use another cell on the sheet calculation (e.g. D4)
2. Select the scroll bar, click properties and set the maximum to e.g. 500 (or even higher) and the cell link to your cell D4 (see 1.)
3. Change the formula in calculation!D6 to =Data!$C$105-ROWS(Dashboard!$E$6:$E$15)+1
This is necessary to avoid circular references
4. Insert a MIN-formula in calculation!D5 to calculate the minimum of D4 and D6 =MIN (D4,D6)
That’s it.
The effect: When scrolling down, the displayed data will stop at the last row of the raw data.
Shortfalls:
a. To be on the safe side you would have to use a rather high maximum value for the scroll bar.
b. The slider of the scroll bar would indicate that you can still scroll down (actually you can, but the displayed data do not change anymore). This might be confusing for the user.
What do you think?
Very stylish. Definitely one to add to the toolbox.
?Borrowing? from some of Jon Peltier chart examples you can quickly add a dymanic display of the measures and show the relationship between the 10 items you see and the rest of the data..
Nice!
(I added a bar chart for KPI 1 with linear trend line on the average of the ten products shown and linear trend line on the average of the 100 products, then hid the series for the averages, made for good "at a glance" understanding)
@Justin:
Thanks for your comment.
You are ahead of your times! Actually adding bar charts with additional lines to the dashboard will be the topic of the 4th post of this little series.
Anyway: I recommend watching out for the next 3 posts. They might include one or two more ideas for you...
have been followin the blog for sometime nw..and it has helped..thnks a zillion!!
@aj ... thanks 🙂 welcome to commenting. Without wonderful readers like you all, this blog is just another site.
started following you from the beginning about excel, i have no other word better than thank you...
Robert, I only just popped back after reading the 2nd post in the series http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/ and I'm really impressed with how responsive you have both been. i'll play around with your solution but it looks great - thanks!
@AdaptiveDervish
Thanks for your comment! Let me know, if you have any problems with the vba-solution or the non-vba-work-around. I think there is little potential for improvement of the vba code, but if you have another / better idea to solve the problem without vba, please let us know.
[...] part 1 & 2 of this series, Creating Key Performance Indicator Dashboards in Excel with Scroll Controls, Adding sort options to excel dashboards before reading this [...]
[...] Creating Key Performance Indicator Dashboards in Excel with Scroll Controls [...]
Hey Chandoo,
I am able to create a dashboard but the issue I am facing is that all the data is coming in the first row of my dashboard one by one when i scroll down.I wrote the offset formula in the first row only.Could this be a reason??
Please suggest.
Many Thanks.
Akash
Hey Chando,
Got it.....many thanks...
Regards,
Akash
@Akash,
just copy down the offset formulas of your first row to all 10 rows of the dashboard table and it should work. If it still does not work in your file, you might want to download the excel file (see above) and have a look at the way it is done there.
[...] 4 post series here at Pointy Haired Dilbert we developed a small dashboard that enables the user to scroll within a larger set of data, to sort by a selected KPI with one click, to identify the best and poorest performers with [...]
[...] Post 1 - Implementing a Scrolling Excel Dashboard Table [...]
[...] excel dashboard tutorials: Creating KPI (Key Performance Indicator) Dashboards in Excel - 4 part tutorial Making Visualizations for Dashboards Too much data? Use tables [...]
guyz this is just awesome .......
[...] Juice analytics lists down 8 features that dashboard makers should keep in mind while designing. Of course if you are stuck at building, do check our tutorials on excel based dashboards. [...]
I was not aware abt how to draw dashboard tables but the published notes/exmple helped me to undersstand /draw table now.
Thanks
Chandoo,
What version of Excel is this tutorial written in or for? I'm on 2010, but I'm struggling with the first step ... building the dashboard table ...
Thanks!
Steve,
the workbooks of this series have been implemented using Excel 2003, but the same technique is possible with 2007/2010/2013, too.
@Azmat and Sanjay: Welcome. I am happy you liked it.
chandoo u r gr8
[...] Excel KPI Dashboards - 4 Post Tutorial and Free Downloads [...]
[...] DIY Excel Training: Learn Excel Formulas in Plain English | Executive Dashboards in Excel - 4 Part Tutorial | 15 Excel Fun [...]
[...] you probably don’t know who he is. Robert is a very exprienced excel user and the author of Executive Dashboards posts on PHD - a 6 post series describing how to create excel based dashboard [...]
[...] Part 1: Creating a Scrollable List View in The Dashboard [...]
[...] Part 1: Creating a Scrollable List View in The Dashboard [...]
Interessante Informationen.
Thank you both for taking the time to put this out for the masses. Can you provide some details on the "calculation" worksheet and why it is set-up the way is. I realize that the result of the maximum position calculation is linked to the scroll bar, but being a newbie I don't seem to understand the reason for the why the max position is calculated the way it is.
Thanks again,
P.J.
P.J.,
Thanks for your appreciation.
You are right: the cell D6 on the calculation sheet is calculating the maximum value of the scrollbar on the dashboard.
The calculation formula is =Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1, i.e.
Number of last entry in the original list (i.e. number or rows in original data table = 100) –
Number of rows on dashboard table (=10)
+1
Result: 91.
Having 100 items in the original table and 10 items in the dashboard table, you can scroll down until the scroll bar value is 91 and the dashboard excerpt displays the rows 91, 92, 93, … 100 of the original table.
But the scrollbar maximum is not linked to the cell D6 on the calculation sheet, because Excel does not allow you to link the parameters of a scrollbar to a cell reference. See also the comment of AdaptiveDervish (comment no. 4) and Chandoo’s and my following comments.
The result of cell D6 on the calculation sheet is used in the formula of cell E16 of the dashboard. Its only purpose is to display the little black triangle beneath the scrollbar to indicate whether or not you are still able to scroll down or not. If you have reached the end of the list (i.e. scrollbar value = maximum value), the triangle disappears.
hi,
i am the beginar for dashboard. why have u used minimum value in offset formula. =OFFSET(Data!D5,Calculation!$D$5,0,1,1)
@Prateek
Have a read of the description of Offset here: http://chandoo.org/wp/2012/09/17/offset-formula-explained/
Robert,
Thank you for the speedy reply and thorough answer. I can't wait to apply your methods to my lastest project.
Thanks again for the very useful material.
P.J.
Hello Robert. I find very useful your series and I'm not an excel pro user. I'd like to use the excel sheets to measure my KPIs monthly. I put in product name the names of the each month and then sorted the data. However when the KPI of a particular month is zero the month disappear from the KPI table. Any comments?
Angel,
I am not sure that I understood your problem correctly: You are using months instead of the product names. That shouldn't make a difference. And you are sorting the table by a KPI. So it seems as if you are using the download file of part 2 of this series. Correct?
If I got it right you do not see a month with a KPI value 0 after you sorted by this KPI. Is this the problem?
If so, the month will be at the end of the list, since the sorting algorithm is sorting by descending order and if you are having positive values only, the month will be the last on the dashboard. Use the dashboard scroll bar to scroll down to the end of the list and check wether the month is down there.
If I misunderstood your question, please write another comment and clarify. Thanks.
Hi,
How did you change the font color to red for negative #s in Data form "dashboard-table-scroll"? I use 2007 and checked the conditional formatting, table formatting and there are none in your example.
I considered myself a knowledgeable excel 2003 person until I found your site: your work is tremendously helpful. Now I am learning 2007.
Thank you.
Alex
Alex,
you do not need conditional formatting to change the font color. It is simple cell number formatting. Go to format cell (ctrl 1) and check out the number format.
[...] on dashboards: KPI Dashboards using Excel (6 part tutorial and downloads), Excel Dashboards theory, principles and [...]
I was just now looking for info about this when I stumbled on your post. I'm just stopping by to say that I very much liked reading this post, it's very clear and well written. Are you considering posting more about this? It seems like there is more fodder here for later posts.
This post is the first article of a 6 post series, but I assume you have noticed that. Actually, for the time being we are not planning to continue the series, but there are a lot of other dashboard examples and templates here on Pointy Haired Dilbert (oops, sorry, Excel@Work). Furthermore you may want to check my blog http://www.clearlyandsimply.com. There are some posts on Excel dashboards as well.
@"w I Got a Free iPhone With Free iPhone Apps" We have a slew of posts on Dashboard reporting using excel. You can find them all here: http://chandoo.org/wp/management-dashboards-excel/
@Robert: no more excel@work.. we have the beloved dilbert back on track.. 🙂 I realized it was stupid to take him off...
very nice - and, as a side benefit, I'm looking forward to going off on my own to learn more about offset.
FYI - I added an AutoFilter to the title row of your dashboard. just to see what would happen. Unfortunately, the vertical scrollbar goes wonky (ie, it stretches out).
I seriously enjoy the content you include on your web site it really has helped me out a whole lot thanks
@Marion: have you read the OFFSET tutorial here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
I am not sure if the data filters on the dashboard would work that nicely.
Hi,
I came across your very informative site while searching for info on dashboards. Thank you for sharing the tips and tricks that help to make programs so much easier to use. Some I knew, but the one that really stood out and I will use quite often is the double-clicking the bottom right of a cell and having it fill series to the end of the data. I've always done this the long way around with edit>go to first and last cell and then fill. This will definately be quicker.
I do have a question, if you don't mind having a look at it. I like the way you have added a scroll bar to a table to reduce the size of info displayed, but wonder if this can be done horizontally. I have a spreadsheet that displays approx 15 products (changes during year) vertically with sales and profit columns for six months into the future displayed horizontally. For now the last month ends on column R. I would like to make a three column table (Vendor, Sales, Profit) and have user scroll to each month's sales and profit columns. I've tried replicating and changing your example but without success. Can you point me in the right direction? I've googled looking for answers, but nothing that seems to answer how I want my dashboard table to look.
Thank you in advance for any advice you may have. Thanks again for all the great tips, templates, etc.
Pam
Hi Pam... Thanks for your compliments. You can add horizontal scrollbar just as easily as you did the vertical scroll bar. While pasting the scrollbar, just adjust its dimensions (ie make its width more than height) and excel makes it horizontal scrollbar. Once you have it, you need to set its properties in such a way that whenever you scroll, the value is incremented by 3. Rest is writing formulas and offsetting the references accordingly.
You can find a horizontal scrollbar example here: http://chandoo.org/wp/2009/03/12/comparison-charts-1/
Is this Excel 2007??
I didn't find tgis forms toolbar anywhere??!!!
Is this Excel 2007??
I didn't find forms toolbar anywhere??!!!
Han,
to insert a form control in Excel 2007 go to the Developer Tab and click on insert and the control you want to insert.
To show the forms toolbar in Excel 2003 click on the view menu and toolbars and select the forms toolbar.
I've gone through the entire series of posts and must say that each part was great - I've implemented all techniques to my dashboard with success. However, I have one little wrinkle with my data set: in addition to the KPIs in my dashboard, each row also has 3 other columns of identifying information (but these are all text so I do not sort by these). For ease of explanation, consider the current example posted but imagine the addition of columns "Group", "Region", "Manager" - and each of these may only have a few different possibilities. For example, for "Region", the 100 different products could be in "USA", "EMEA", or "APAC" - is it possible to add functionality where the user can filter by this "Region" column and now the dashboard will only show rows that are in any of the given regions selected (but still only 10 rows at a time)?
John,
thanks for the compliment. Yes, filtering is possible to some extent, but this is hard to describe within a comment. I uploaded a file for you that might help you implementing what you need:
http://www.box.net/shared/bkvn5qjoye
The drawback: After filtering by a region, the user is still able to scroll down to row 100. Nothing will be displayed in these rows, but this might be confusing...
The problem: as mentioned above in other comments, you cannot change the maximum of the scroll bar without VBA. If you want to avoid VBA, this is the only way I know to get what you want.
Let me know what you think.
Hi Robert,
The implementation you showed is what I'm talking about - how would the approach change if there were other columns/fields that the user would like to filter by (e.g. in addition to filtering by region, the user might also like to filter by a column such as "Manager")?
John,
here is the modified approach using 3 filters (region, manager, sales channel), but still having the disadvantage metioned above:
http://www.box.net/shared/ffhf06d32f
Let me know, what you think.
Robert,
I just tried your solution - exactly what I was thinking of...thanks for sharing your workbook - it enabled me to figure out the algorithm/calculation to display only the entries for the filtered categories. Many thanks! I may end up adding the little vba code as described in earlier comments to limit the max amount of rows displayed.
[...] KPI Dashboards using Excel – 6 part tutorial [...]
hi Chandoo!
Thanks for excel tricks! It realy helps me. Well I am working on a dashboard.I am not able to link my cells. When I do right click on the scroll bar I get the format option but not the control where i can go and link the cells. Please suggest how I can view the control option. I am using excel 2003.
Thanking you in advance!
Manish
[...] Scrolling report – Chandoo [Link] [...]
[...] Scrolling report [Link] [...]
[...] 6 Part Tutorial on Making KPI Dashboards in Excel [...]
[...] vele dashboards op die vrij te gebruiken zijn en daarna zelf te bewerken zijn. Een voorbeeld is een goed dashboard om KPI’s te meten. Ook staat er van iedere mogelijk denkbare chart wel een voorbeelden kun je iedere formule [...]
I really love the website but I have not been able to get the scroll to work. I think these post are not step by step. Can you please make them a little more detailed? I am having a lot of difficulties getting any of them to work.
@Manish.. Sorry, I didnt notice your comment until now. Do you still need help?
@Veronica: Welcome to Chandoo.org. Can you tell me where exactly you are facing the problem?
I too am having trouble following this. I guess the people who are able to follow this are for more advanced on Excel than me. I think your site is great and you are very very good at what you do, but I do agree with the above poster, a more detailed step by step guide would be useful for somebody like me.
Thanks
@Alexander: I am planning to do a small video course explaining this and other techniques to create excel based dashboards. Please give me a couple of weeks as I am still working on the mechanics of this. 🙂
Hi Robert,
I`m playing with your KPI Dashboard revised II.
Very nice dashbaord. I will use this chart in future.
I need this tool with a new drop down for different customers.
Let me know, if you can help me.
Thanks
Andre,
sorry for responding that late. I just noticed your comment.
I suggest adding a drop down at top of the dashboard for selecting the customer. Then, add another worksheet and use OFFSET formulas to fetch the relevant data for the selected customer from the data worksheet. Change all formulas on the calculation worksheet to refer to the new worksheet with the selected data.
Here is an example:
http://www.box.net/shared/5d9t5hfb1x
I hope this will be helpful.
Robert,
thanks for your help. Your Example are very good.
Now, I can better work with this dashboard.
I have another 2 questions for optimaze the dashboard for my work.
1.
I have different numbers of products for the different customer. I think this is a problem for the combinated bar/line Chart. The minumum and the averages is not okay. Which is the right way for this problem?
2.
I have in my data the productname, manufactuers and brands (800 products from different brands and manufactures). Now I will analyzie the hole products and in the next step only for one manufacture or for one brand. How I can complete this KPI dashboard with the different selections?
Let me know, if you can help me.
Thanks
Andre,
you are welcome.
With regards to your questions:
1. Different numbers of products for different customers
You are right with regards to the calculation of the averages. You have to calculate the number of products for each customer (e.g. using COUNTA) and change all formulas calculating averages. An OFFSET function using the calculated number of products of the selected customer could dynamically change the cell range to be included in the AVERAGE function. Furthermore you have to make the data source of the XY scatter chart dynamic as well (again using OFFSET functions).
2. Products, manufacturers and brands
One option would be inserting a Pivot Table and retrieve the selected data from there. Another option would be additional drop downs to select manufacturers and brands and to use array formulas to apply the filter on the data used for your dashboard. Finally you could also use VBA code instead of the array formulas to filter the data. There is always more than one way to skin the cat, so probably there are other possibilities as well.
I hope this will be helpful.
Thank you for your advice.
I`m finished my first problem with the averages.
Can you help me with the dynamic XY scatter chart and by the combination with the drop downs for customer, brands and manufactures? I dont understand this part.
Thanking you in advance!
André
Andre,
you will find a detailed tutorial how to create a dynamic chart over at Jon Peltier's website:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
Excellent site and tips for creating dashboard!
I am running into one challenge. How do I seamlessly intergrate a vertical scrollbar and a horizontal scroll bar. I started with the vertical and used the OFFSET formulas sucessfully. I also have the sort feature. Now I realize to take my dashboard to the next step, I need to also have horizontal scrolling. Since all my cells already have a OFFSET formuals and have the sort logic, is there an easy way to make this formula to also switch to horizontal scrolling and keep my sort logic intact ?
Thanks again !
Milind,
I think I already answered this question in a comment on the second post of this series. Have a look here:
http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/#comment-62805
Hi Robert,
thank your for your Link to the dynamic Chart of the Website
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
After many hours I finished my work. I don´t found the right answer.
Please help me.
I need a second drop down menü "Manufactures" in the dashboard in Cell J3.
http://www.box.net/shared/5d9t5hfb1x
In my data page I have the differenct manufactures after the product name.
Let me know, if you can help me.
Thanks André
Andre,
you are writing that you finished your work but didn't find what you have been looking for. The link in your comment, however, takes me to my own example with the additional customers drop down.
If you will upload a file with the work you have done so far and describe where you are hitting a road block, I will have a look at it and give my 2 cents.
Thanks Robert!
Got another question and would appreciate your suggestion. I now have 3 scroll bars in my excel dashboard, complete with the sort logic and all. They all have an equivalent to "Product Name" that can be scrolled down to see different KPI's. Can I put a search bar at the bottom of my dashboard with a button or so, that when I type in a specific product name and hit the button, all my 3 scroll bars should scroll down or up and find the "product name" while keeping the sort order that it was being sorted on.
Thanks again!
Milind,
you will need some VBA to do this.
In a first step you could use a cell at the bottom of your dashboard as the entry cell for your search string. You could then write a MATCH formula in another cell to find the position of the entered search string in your data. But now you need VBA to overwrite the target cell of your scroll bars with the number the MATCH formula brings back. If you want to have a scroll bar and a search functionality at the same time, I do not see a way of doing this without VBA.
Thanks for suggestion. So once I use the MATCH formula, is there a simple VBA code that I could use to overwrite the cell link for the scroll bars, when the search is triggerred.
Or is this too involved ? I am not good at writing VBA code at all...if its too complicated, I will probably drop the feature of having a search funtionality.
Thanks for your help!
@Milind
As Robert said, If you setup a cell where you can type your search term and then use a Match to lookup the value from a validation list,
You can then use something like this:
Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = "$B$10" Then
[C1].Value = target.Value
[C2].Value = target.Value
[C3].Value = target.Value
End If
End Sub
The above code must be pasted onto a code page for the sheet you are working on, not a general code module.
Change
B10 to the cell which has the Match formula refered to above
C1..C3 are the 3 Scroll Bars Cell Links cells, Change to suit.
Hui…Thanks for suggestion. Please excuse my lack of VBA skills…Here’s what I have now. I wanted to use the search feature for one of the scroll bars first.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$37" Then
Sheets("Calculation").Range("$E$5").Value = Target.Value
End If
End Sub
Nothing happens…So, I have a button (form control) next to my search box and I right-click to assign macro that will get triggered when the button is clicked. (I had used this same approach for controlling the max value of data in the scroll as I have a combo drop down list for the scroll )
This is what I have as the macro code when I click on the search button
Sub Button4759_Click()
If Target.Address = "$F$37" Then
Sheets("Calculation").Range("$E$5").Value = Target.Value
End If
End Sub
So, now I am getting a run-time error and the code stops with yellow markers on “If Target.Address = "$F$37" Then”
F37 is the result of my MATCH formula and Sheet(Calculation.E5) is my cell link for the scroll bar.
Please let me know if you have any suggestions. Thanks !
Your original code below
`Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$37" Then Sheets("Calculation").Range("$E$5").Value = Target.Value
End Sub`
Has to be on a Worksheet Code page not on a Code Module
That is in VBA Double Click the worksheet you want this to apply to and paste it on the Right Hand pane
Your scroll bar needs to have Calculation $E$5 as the cell link
That worked just great ! Thanks again!
Milind,
I like your idea very much. Although Hui beat me to answering your questions (thanks Hui!), I decided to write an article elaborating on different options of how to implement this search functionality:
http://www.clearlyandsimply.com/clearly_and_simply/2010/09/scroll-and-search-in-excel-dashboard-tables.html
I thought you might be interested.
Robert - Thanks for taking the time to detail this out ! The entire concept of scroll, scort and search to display as a dashboard is Way cool
My question has to do with the chart used on the dashboard. When I try practicing building the dashboard I add a chart and go to select data the options are different. I.E. you chart for the average has series name, series X values, and series Y values but when I add a chart it only ives me series name and series X values options. How do I getthe option for series Y? I apologize if you already answered this I did not see a previous post. Thanks for your time.
Steve,
the charts are using 2 different chart types: a horizontal bar chart for the values and an XY scatter chart for the average line and the scale dummies. If you add a new data series to the chart it will be a bar chart by default. You have to change the chart type to an XY scatter chart and then select the data sources for the X and Y series.
I hope this will be helpful.
[...] Creating KPI Dashboards in Excel – 6 part tutorial [...]
your dash board formula is not working and giving error of reference is not valid
@Jawan
Can you be more specific about what your problem is, as hundreds of people have downloaded this without problem
Can a similar dashboard be constructed, but with filtering capabilities? I want the charts to depend on the multiple filters selected by the user.
Hey!!...gr8 job man...
this help me a lot...
Looking forwrd for coaching. kindly guide me.
Cont no 9999066800
Ryan,
have a look here:
http://www.box.net/shared/5d9t5hfb1x
Something like this?
Great website, here comes the silly question so apologies now:
How can you change the scroll window to show more than 10 rows?
Neil,
no sweat:
1. Insert empty rows somewhere within the table on the dashboard, e.g. the row above the last row. Let's say you insert 10 additional rows.
2. Copy the formulas from the row above the inserted rows down to the last row of the table.
3. Increase the height of the scrollbar until the scrollbar covers all rows again.
4. Right click on the scroll bar, go to the control tab and change the maximum of the scrollbar (e.g. to 81 if you have 20 rows instead of 10).
I hope this will be helpful.
Hi Robert
That's great much appreciated, how do you re-align the chart to the correct rows is it a case of manually adjusting the size to get them to align properly?
Thanks Again
Neil
Neil,
in the end you have to some manual alignments to make the charts look good within the table.
There is one helpful trick, though: Keeping the ALT-key pressed during moving and resizing a chart will make the edges of the chart snap to the grid of the worksheet. This helps positioning and resizing the chart. In Excel 2007 and 2010 this works not only for the chart itself, but also for the plot area.
And this works for all objects in an Excel worksheet, by the way, shapes, form controls, ActiveX controls, etc.
I hope this will be helpful.
Thanks Robert worked a treat.
Sorry for my noddy questions new to dashboard creation.
Keep up the good work!!
Neil,
no need to apologize. There is no such thing as a stupid question. However there are bad, unclear, insufficient explanations...
You are most welcome. I am glad you liked it and I am happy to help.
Hello
Thanks for the tip very handy. Is there a way to link it to a drop down filter in a dashboard? For instance say you have a drop down of years in the example, and you have 100 different years you want to link to. Is there a way to do that so if you change the year you change the data in the table. It would look something like this...
Years [Drop Down Filter]
KPI 1 KPI 2 KPI 3
1 Product 1 XXX
2 Product 2
3 Product 3
Thanks for any help you can give me.
Joe,
have a look at the file provided for download in my answer to Ryan's question (comment no. 75).
Does this answer your question?
[...] with Robert Mundgil of clearlyandsimply.com. Robert is an Excel wizard. You may know him thru the KPI Dashboard articles he has written on chandoo.org a while [...]
Hi Robert - Is there a simple VBA code I could use to create a mouseover effect when I hover over a range of cells that form my scrolling list ? What I want to do is provide some neat information specific to each "Product Name" when I hover the mouse on that cell. Ofcourse the pop-up needs to disappear when I am not on that range of cells.
Any help would be greatly appreciated ! Thanks as always
Milind,
the easiest way of doing this is using cell comments. Instead of writing a detailed description here in a comment, I uploaded an example:
http://www.box.net/shared/c5trieh1tz
2 simple worksheets, 2 range names, 2 command buttons, 2 simple VBA subs. Have a look and let me know if you have any questions.
[...] Below that I have used Scroll Bars (2 & 3) to allow a lot of data to be shown in a small space, scroll bars do this very nicely. The data in here would be a list of all the products within the current range of orderable parts ( the detail behind the table and bar chart above ). [Related tip: How to create a scrollable list in Excel Dashboards?] [...]
Thanks Robert. I think that will work just fine. I am going to give this a try. Another question...Is it possible in the same VBA subs to hide the small red triangle that you see on the upper left corner of the cell (that denotes that there is a comment ) ?
Thanks as always !
Milind,
you are welcome. I do not know a way of how to hide the red triangle indicator for commented cells. I am pretty sure that there is no way to do this with Excel options. I am not that sure with reagrds to a VBA solution, but I doubt there is a way to hide the indicators in Excel. Doesn't mean there isn't a workaround. I just don't know one. Sorry.
Milind,
on second thought: if you are really getting the needle with the indicators, another possible approach would be using the input message of the data validation functionality. However, I am not sure that this would be worth the effort. Just an idea.
Robert - After some googling, I got this...but can't get the macro to trigger. Would something like this work and how should I invoke it ?
Sub RemoveIndicatorShapes()
Dim ws As Worksheet
Dim shp As Shape
Set ws = ActiveSheet
For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
If shp.AutoShapeType = _
msoShapeRightTriangle Then
shp.Delete
End If
End If
Next shp
End Sub
Milind,
I guess you have taken this piece of code from Debra Dalgleish's website?
http://www.contextures.com/xlcomments03.html
Debra solves the issue by inserting rectangles (shapes) to cover the comment indicators. There is a sub called CoverCommentIndicator on Debra's site directly above the sub you copied into your comment. The routine RemoveIndicatorShapes only removes the rectancles that cover the comment indicators again. You have to run the CoverCommentIndicator sub first to "hide" the comment indicators.
[...] original KPI dashboards using Excel article was so popular. They still help around 12,000 people around the globe every month. Many of [...]
Robert - When I try to use the code that you had for assigning the comments to myproducts, I get the error " Object variable or With block variable not set", when the code is trying to execute the statement
rng_Target.Cells(lng_count, 1).Comment.Text rng_Tooltips.Cells(lng_count, 1).Value
Any ideas ?
Thanks again !
Milind,
I suspect you didn't define the range names (myProductNames and myToolTips) or you defined different names than I did and didn't change the Set statements in the VBA accordingly.
Robert - Just to continue the dialogue...
I got the names defined correctly as well as referenced them in the SET statement. I have been researching this error in several posts, and it looks like for whatever reason the excel VBA is considering that line of code as an object variable in play that is not being referenced....still combing through all these posts.
Thanks
Hi,
When I add a product 101, this one is not added dynamically.
Is there a way to achieve that ?
I've seen that in the calculations sheet we use the following formulas (=tblKPIs) but this one does not automatically expand.
Moreover I'm not even able to extend the table myself ?
Could someone clarify ?
thanks
[...] KPI Dashboard using Excel – 6 part tutorial [...]
Milind,
if the code is the same and the names are defined, it should work. I can't tell you why it doesn't. If you have an anonymized version, you can send me your workbook (you will find an "email me" link on my blog Clearly and Simply) and I will have a look.
Philippe,
since you are talking about tblKPIs, I suspect you are referring to Chandoo's revisited version not to the version posted here, right?
Chandoo is using a table in Excel 2007 and you can expand the table on the worksheet data by simply clicking on the handle at the lower right cell of the table and dragging this down. If you do so, the name tblKPI will be updated automatically.
However, you have to adapt all cell ranges on the worksheet [calculation] as well, because the calculated ranges (in rows 20 to 119 in Chandoo's workbook) only cover 100 entries. Best way of doing this is inserting as many new rows as you need above the last row of the calulcation range and copy down the formulas from the row above.
Finally you have to increase the maximum value of the scrollbar on the dashboard.
I hope this answers your question.
Yes. Robert you're right I was looking at the revised file of Chandoo.
But my question remains.
How can have a dynamic scrollbar ? How can we give the revised example of Chandoo to any user without having to update the file for him when he will add a product ?
I used often vba & pivot table to cope with this situation but I'm wondering if there are other solutions.
For example: 1) If new products are added, I have to change the maximum value of the scrollbar. 2) Same in the calculation sheet in Chandoo 's file where there is the "tblKPI" array that I will have to extend
T
Philippe,
there are different ways of solving this. For instance, you could use named formulas to create dynamic ranges to be included in your calculations. One COUNT or COUNTA formula to detect the actual number of rows in the data worksheet and named formulas using OFFSETs based on this actual number of rows. Also, you have to perform all calculations for the sort by formulas (Chandoo's rows 20 to 119) in dynamic named formulas. Some more work to do, but this gives you more flexibility if the amount of data changes.
Another approach would be using a VBA sub to detect the number of rows and automatically perform all the steps I described in my comment above.
Finally: the problem with the scrollbar and the maximum value: Have a look at the discussion in the comment section above: comments no 4) to 9).
Hello-
I cannot get the data to scroll. I'm not sure what I'm doing wrong...I've downloaded the form and tried it on my own...no luck. the max value does change when it scrolls (on the calculation page), but, the data doesn't scroll!?
Any help is appreciated. Not sure if I should format any of the data?
Thanks
G
G,
the target cell of the scrollbar has to be D5 on sheet calculation, not D6 (the maximum). The OFFSET formulas on the dashboard use calculation!C5 to scroll through the data. The maximum formula in calculation!D6 is only used to display the little triangle below the scrollbar.
As a noob to details about Dashboards (only familiar with very general concept) I have a simple question. What ... is a KPI? You mention it 59 times in the 6 parts, but never define it.
OK, I googled it: KPI = Key Performance Indicator (found it on another of your pages, <grin} )
Hi Robert!!!
This is an awesome tutorial, thanks a lot for sharing.
If I have about 13 KPI's to show in the same area, how can I put an horizontal scrollbar to get it?
I want to use this scrolling idea but adding a new horizontal bar.
I appreciate your big efforts to share your knowledge with us.
Thanks a lot Robert!
King regards,
pibfer
Very nice article!
I am wondering if it is possible to use OFFSET formula another time in another table but in the same worksheet. For example if I would display two kinds of tables with both their own scrollbar? Now I end up with one scrollbar scrolling two tables, while the other one is not working, very inconvenient.
Any help appreciated!
Kind regards,
Johan
@pibfer,
thanks!
This has already been answered in a comment on the second post of the series. Have a look here:
http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/#comment-62805
The comment is providing a link to an example worksheet. I hope this will be helpful.
I hope this will be helpful.
@Johan,
thanks! Sure, this is possible. Simply link the second scroll bar to another cell (i.e. the target cell of the scroll bar) and use this cell in the OFFSET formulas for your second table. The technique is exactly the same, all you need is another target cell for the second scroll bar.
[...] Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel. [...]
Lots of useful info on your site - I found this article especially helpful 🙂
I am using Excel to compensate for not having systemic reporting on the status of products for up to 50 affiliates. Rather than create 50 separate reports, I have created one master report that they can filter by country. I am trying to make it all less intimidating and more relevant by structuring the data in a dashboard, which ideally would include a scrollable table limited to only their records.
I have successfully reproduced the table, but my question is, is there any way to make such a table editable :-). Since the actual table is filled with formulas and not values, editing the cells would just write over the formulas and ruin the table. I want to use the table to focus on their products with issues (a small percentage) and have them write respective comments concerning each, without forcing the user to go to the source table, which may have thousands of rows in it... (they have to 'vet' the products... they could do that either by a comments or Y/N field, or by highlighting with cell fill - but none of these means will work on the formula-filled table...)
Ideas? 😀
Angela,
if I understand your requirement correctly, you want to fetch data from a raw data table based on a filter (the affiliate), display it in a scrollable table on the dashboard and let the user add some comments to the data. This is possible, but I can't see a way of doing this without VBA.
You can easily add one or several columns to the table of the dashboard (without formulas) and the users can write their comments to these columns. The challenge, however, is writing these user inputs back to the raw data. A small VBA sub can do this, storing the inputs on the raw data table e.g. after every user input (or after scrolling up/down, leaving the dashboard, closing the workbook, etc.). As I said, I do not think this will be possible without VBA.
The only way I can see to do this without VBA is working directly on the raw data sheet using an autofilter. Yet, you won't have a scrollable table on the dashboard then.
Thanks for replying so quickly Robert! I really don't mind using VBA and I've used it often in the past...You said, "You can easily add one or several columns to the table of the dashboard (without formulas) and the users can write their comments to these columns." But if there are no formulas in the columns, then how is the scrollbar going to keep the comments in sync with the rest of the data in the dashboard table? After the user adds their comment, as soon as they scroll up or down, their comment will now appear adjacent to data from different rows that the refreshed OFFSET formula is reflecting from the raw table.
It seems to me that using VBA, this is the minimum required to solve the problem:
1) add the "comments" column to the source 'raw data' table 2) make sure this column is included and visible in the scrollable table using the OFFSET formulas 3) have a VBA macro run on worksheet_change event 4) if the change occurs in the target range, then the macro will write the value to the corresponding row in the raw data column 5) the macro then replaces the value in the target range with the original OFFSET formula that was overwritten.
If there is a better way to do this please let me know. If not, I will give it a try next week and let you know how it works out... 😀
Angela,
instead of a lengthy description in a comment here, I created a very simple example workbook for you. This is the download link:
http://www.box.net/shared/puz160r5k1jg7teqg7d6
The VBAis based on 2 very simple subs called GetComments and StoreComments (one line of code each), fetching the comments and storing them back to the raw data table.
Please notice that
1. I defined 3 named formulas (named ranges) to be used in the VBA.
2. The subs are not only called in the Worksheet_SelectionChange and the Worksheet_Activate event subs of the dashboard worksheet. The GetComments sub is also used when using the scrollbar.
3. The example is a simple as can be. The filtering by affiliate you mentioned in your first comment is not implemented.
I hope this will be helpful.
I get it... the scrollbar is assigned a macro that places the appropriate values from the raw data table into the dashboard table (Sub GetComments). When the user enters data triggering a worksheet_change event, the StoreComments routine is called, which resizes the raw data comments range and writes the 10 dashboard comment rows to it.
I modified your sheet to implement my idea, which is an alternative solution. Basically the comment col in the dashboard is filled with the OFFSET formulas on open of the doc. No macro is assigned to the scrollbar, and I use only the "StoreComments" sub and added one line to it:
Range("E5", "D14").FillRight
[if you want to use named ranges, you can name E5:D14 and it works the same:
Range("myFillRange").FillRight
]
They both have pros and cons. The version with the formulas doesn't have a 'lag' for the comments col to update when the scrollbar is advanced. If the user decides to hold the up or down button on the scrollbar, the formula version scrolls smoothly while the values version appears to 'freeze' the comments col while the other columns scroll along speedily. So formulas are much better for the 'scrolling' user experience. However, the values version is superior if the user, after entering in a long comment, decides they want to re-select the cell to edit just a part of it. If the cell is now filled with formulas, when they double-click it they will see the formula instead of getting a cursor and being able to select portions of the text.
Since I may only be asking the user for boolean Y/N values, I might go with the formulas. But if ever I would require the user to enter lengthy comments, your version is definitely worth considering despite the lag time. Thanks again, very helpful... looks like I'm on my way to a very user-friendly dashboard 🙂
Angela,
well there is always more than one way to skin the cat, isn't it?
To be honest, I think the only downside of my solution is the fact that the comments column of the dashboard does not update when keeping the mouse key pressed on the scrollbar (because the GetComments sub is only executed after the scrollbar lost focus, i.e. the mouse key is released). But I think this does not really matter. I would expect, if a user searches something he will click several times on the scrollbar. If he keeps the mouse key pressed, I think it doesn't matter that the comment values are not updating. The user is just trying to jump to another part of the table quickly and will not really look in detail to what is displayed during scrolling.
Anyway: your workbook, your call, of course.
If you are interested in some more ideas on how to improve the usability of your dashboard, you may want to have a look at the following article on my blog:
http://www.clearlyandsimply.com/clearly_and_simply/2010/09/scroll-and-search-in-excel-dashboard-tables.html
This might be an interesting additional feature for your workbook.
Thanks Robert for all your help! And thanks for posting the link to your article on the search functionality - very useful functionality to have.
Angela,
You are most welcome. Let me know if you have any further questions, either on the KPI dashboard articles here or on the articles on Clearly and Simply.
Hai Chandoo,
U R Just Awesome
Hi all in the scroll KPI , how add more kPI column ,
@Thowfeek
Insert a column/s on the data page as required.
Insert data
Insert Columns on the dashboard page in the same positions
Copy formulas across from a column to the left into the new column/s
Hi Hui
thanks
i had tried same but scroll not working properly after KPI 5
pls guide me
@Thowfeek
I suspect that you added a Column to the end, not Inserted a column as I suggested.
I have emailed you the file with a column added
Amazing model... Great visualizations but one fatal flow: neither this nor the Excel 2007 revised version posted cannot handle more than 100 data points dynamically.
I modified this to use a dynamic Excel 2007 table range and set the formulas so that it can automatically expand if more rows are added to the data table like you'd have in a situation where you were using data served by MS Query.
The ONLY macro I had to add was to set the maximum scroll threshold in the scroll bar.
@Brian
Did you change the maximum value of the scroll bar ?
Right Click on it and Format Control
Change the maximum value to say 120 or what ever you want.
unfortunately there is no way to link that to a cell or formula.
I had to resort to VBA for the Maximum value but at least it is dynamic.... the macro counts the rows in the table data (refreshed from MS Query so it's dynamic) and then sets the scroll max to the number of rows... too bad other than that it stayed true to using no macros
Hello,
I love this dashboard, but I cant seem to figure out how to add more products, I am trying to get this to work with 1000 products, but I cant seem to figure it out, any help would be greatly appreciated.
Thanks
@Nabil
.
Insert the extra rows on the data page as required.
Make sure you insert the extra rows before Row 105, Don't add it to the end
On the Dashboard Page right click on the Slider, Format Control
Change the Maximum value to suit your new data (Number of rows)
.
You may also want to read Post 117 above, How to add extra fields
Thanks for the help, I did what you recommend but now I get a large white section in the chart on the dashboard for KPI 4
Any recommendations?
@Nabil
Did you populate the new rows with extra data?
.
What Chart ?
This post doesn't have a chart ?
Yes I did it its working ok now, I was talking about the Max, Min, Average and Targets per KPI bar charts.
Also I was wondering if there is any way to have the Product names of the top 10 how up on the scatter chart when the two KPIs are selected? As it shows 10 displayed in red but its hard to know what those are, am thinking if its possible to have the name show up if you scroll over it?
The white space on the bar charts is called "Series Invisible Point KPI 4 value 256" its showing up on the KPI 4 bar chart.
@Nabil
If you email me the file I'll have a look at your questions
Thanks again to everyone, especially Hui for all your help on this. I have another question and it probably wont be the last, I tried to change some of the KPIs to percentages in the data by formatting the cells and values. however this is not reflected in the dashboard as percentages. Any assistance would be greatly appreciated.
Also would like some thoughts on changing the last KPI to show inventory levels, I was thinking would it be possible to show the levels in a format like a gas tank? So when the levels get low ( have this as an assumption to input by the user) its shows up on the dashboard as amber, really low as red and green for ok. How could this been done? would it be a hard challenge?
Another idea I had was to have another section added to show bands. these would show what percentage of total sales would be within a certain price range. Then to set up the price ranges as an assumption. 0 -50, 50-100, 100 - 200, 200 to 500, 500 to 1000, 1000 and above. Not sure what would be the best graphical representation of this.
Lastly had an idea to have a cloud showing product names in terms of how slow the inventory is moving so these would be in red and then to have a cloud to show what is moving the fastest in Green or Black. By cloud I mean it would have all the product names and the larger the volume of sales the larger the text. And the opposite for the non moving items. Not sure if this can be done via Excel, but it would be a cool addition for sure.
any help and or comments are appreciated and if any excel geniuses out there can do the suggestions I put please contact me and i would love to work on this and would be eternally grateful.
"I tried to change some of the KPIs to percentages in the data by formatting the cells and values."
You also have to change the same fields in the calculations and on the dashboard pages so that they all match
.
The Minimum, maximum, averages and targets per KPI are not setup to handle negatives properly
In Calculation!BF23: =IF(BG23<0,0,AZ23)
Copy down
I truly appreciate the material you have on your webpage it truly has helped me out a lot thanks
Thanks again,
Do you think its possible to do the cloud idea and the gas tank indicator as per my previous post?
Also shouldn't the formula for In Calculation!BF23: =IF(BG23<0,0,AZ23) be In Calculation!BF23: =IF(BG23<0,0,BA23) ? Right now it changes the values to "KPI1", "KPI2"
Possibly ?
I had already started thinking with another idea and had added columns and maybe didn't allow it correctly when I answered
.
As for the Gas Tank have a look at: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/
.
As for the cloud, Yes It can be done provided you limit it to a known number of items.
I'm not sure about the animation bit, It can be done but sounds tacky!
@Nabil
Not sure if this helps with your bands problem?
http://peltiertech.com/WordPress/excel-charts-with-horizontal-bands/
[...] KPI Dashboards in Excel [...]
[...] KPI Dashboards in Excel 112,683 [...]
Greetings,
Has there been any new methods of getting around the max value issue mentioned in comment 4. I have tried Robert's suggested VBA but am getting a compile error when I run it. I'm using excel 2010.
Ramon,
the code I provided in my comment above works like a charm for me in all versions of Excel. What kind of error do you get? Can you post your example workbook for download somewhere?
Hiya,
This stuff will come in really useful thanks ever so much!
In the mean time, I agree with some of the comments above. A more step-by-step guide would have been helpful ie do you create the Calculation tab first, last or does it not matter?
I am finding that when I recreate this I am able to create the scrolling table (which I guess is the main objective!) but on the calculation tab the formula goes missing and resets to a scrolling number (if that makes sense!) I have copied your sheet like for like but it still does it and I can't work it out! Any ideas?
The fact that I can successfully create the scrolling table means I should be happy, but it is bugging me! lol
Ksandra2901,
I agree, a more detailed step-by-step may have been helpful, but you still have the workbook for download and it is not a very complex one. On to your specific question: the usual way of creating such a workbook is
1. Input data
2. Calculations
3. Visualization / Dashboard
What do you exactly mean by "the formula goes missing and resets to a scrolling number"? Are you referring to the target cell of the scrollbar? Actually there is no formula, because this cell is used by the scrollbar and all formulas are based on the actual value of this cell.
Can you please clarify what exactly your problem is?
Hi thanks for replying. I've sorted it now thanks!
Hi Chandoo & Robert,
I am basically new to excel and just stumbled around on this website by chance. I am currently in my 1st year of MBA and the excel stuff out on this website really amazes me! I never knew a scroll bar can do such stuff. 🙂
Awesome stuff. You guys rock \m/
[...] KPI Dashboards in Excel – 6 part tutorial [...]
This website is really amazing. 🙂
when i select scrollbar from developer insert menu. In format control option there is no "control" option because of that scroll bar is not working. i am using excel 2010.
pls guide how i activate scroll bar
It sounds like you have selected an Active X Scroll bar not a Form Controls Scroll Bar
Try inserting the other scroll bar type (at the top of the Insert menu)
Thanks for your link. It's useful for our community.
Same material can be found at : http://keyperformanceindicators.info/airline-kpis/
I hope it's useful for you and you like it. Please continue sharing more information at this topic.
Best rgs!
[...] KPI (Key Performance Indicator) Dashboards in Excel – Tutorial [Part … Link to this post!No related posts. [...]
[...] KPI Dashboards using Excel – 6 part tutorial [...]
I love you chandoo . Because you are professionally exciting and very intelligent .
Thank you for your labouruous site
how to make a dash board in excel ?
Is that dashboard is not the part off excel ?
[...] KPI Dashboards in Excel – 6 part tutorial [...]
[...] KPI Dashboards in Excel – 6 part tutorial [...]
[...] Charting & Dashboards: Dynamic range charts | Top x chart | Analyzing large datasets | KPI dashboards [...]
Great post! I was wondering if a form button (that uses a macro to go to another page/chart) can be used in the scrolling list? I tried but cant figure out the formula. Any help would be greatly appreciated.
@Gigi... You need to apply this new style to your table.
Gigi, Chandoo,
I have to admit I do neither understand Gigi's question nor Chandoo's answer.
@Gigi: Do you mean you want to have a button in each row of the table which would start a specific VBA routine for exactly the data record of this row? If so, you would need additional VBA code to change the .OnAction property of each command button in each row after changing the scroll bar.
Can you explain in more detail what you are looking for?
I am sorry for the messup. I was replying some other commenter and it ended up here. I guess I was too sleepy.
As Robert said, you need either VBA or creative use of HYPERLINK() so that you can dynamically redirect user to relevant drill-down / chart which row they clicked. For more on HYPERLINK based solution, see this example and apply similar technique
http://chandoo.org/wp/2011/07/14/dynamic-hyperlinks-in-excel/
Hi Chandoo. Can you please tell me how did you type "?" in the cell(E5, E16) formula. I hope it is not symbol!
This will be highly while getting increments and performance appraisal 😉 DATA talks more
[...] KPI Dashboard [...]
[...] avec des exemples c'est plus facile ... KPI (Key Performance Indicator) Dashboards in Excel - Tutorial [Part 1 of 6] | Chandoo.org - Learn M... d'autres exemples : Excel Dashboard Examples - 66 Dashboards to Visualize Excel salaries around [...]
[...] dashboards – Information, examples & tutorials Creating a KPI dashboard using Excel – 6 part tutorial Sales dashboards – 32 examples & [...]
Hi Chandoo!
We also use to make this kind of dashboards few months ago, with our Excel power user. Then we had too much data to keep on using Excel. We decided to create our own dashboarding solution, jolicharts:
https://jolicharts.com/
It is now open to every one, and you can use it with a free account. Upgrading from Excel dashboards to Jolicharts was a little revolution for us: It increased (a lot) our productivity as well as the dashboard quality of our Saas products.
You can easily connect your data or databases (no size limit), create KPIs from charts and dashboard, share these dashboards, even embed it in your software if needed! Do not hesitate to give us feedback!
Cheers
Hello,
I'm trying to implement the scrollable table however arose a problem where one of my columns has some blank date fields. Though instead of just pulling the blank data "" with the offset function, it is populating the corresponding field in my scroll table with a phantom pseudo date of 1/0/1900. ;-p There's not even so much a zero in the field I'm referencing and the fields in both tables (i.e. scroll set up table on my summary tab and the main data table) are formatted as dates. I did try the isblank to no avail unfortunately.
Can anyone please tell me why I'm getting this error? My formula is below:
=if(isblank(OFFSET('Inventory Status'!k1,Sheet1!!$Z$1,0)),"",offset('Inventory Status'!k1,Sheet1!!$Z$1,0))
Also, while I'm here, some of the rows in main table got hidden but the scrollable table forces those rows in anyway, is there a way to avoid that?
I also was wondering if there was a way to filter the table by the click of a button (say radio button option) where you can click on or the other to filter down the scroll table to only show properties that are Under Contract or when clicking the other radio button to get only properties that are actively listed and not under contract
This site IS Awesome btW!!
Is it possible to pull data from two different sheets using the offset formula? I have created the scrolling table using Sheet 1 of data, but now want to include columns in my scrolling table with data from Sheet 2 of data. Sheet 2 and Sheet 1 both contain similar data fields, but in different positions, so you could reference say the matter number. Any help is appreciated!
Great site by the way!
@Jason
Absolutely
Generally setup a report with columns for fields and source each from wherever you need to
I think I know what you mean. However the second report has two rows for each instance, I want to lookup based on the matter number and return the value say in column g on the row below.
Only option may be to match/index the data from sheet two into sheet one then reference the new column in sheet one in the table.
@Jason
Can you post a sample file
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Great Information Can't Watch for Another Post. Great Stuff
Hi.
GREAT SOLUTION!!! I'm an addicted Excel user, but not a pro. Implementation was easy!
I'm having a problem tough.
When I click the scrolbar arrow, it keeps scrolling until i remove my mouse pointer off the button. I've found this post: http://chandoo.org/forums/topic/strange-behaviour-with-scrollbar-control#post-107568
Manual Calculation is not an option, because of the offset formulas in the "scrolling area'.
I've tried using an ActiveX Scrollbar instead, but this doens't works completely as expected and is VERY slow.
Is there any solution?
Hello,
Is there a way to add the name to pop up when you click on one of the metrics on the comparison scatter chart?
Great dashboard, btw.
Thanks!
Robert,
I need help with a dashboard simulating a data base of an airport, using the information of passenger and flies. We have a database on Access and need to export it to Excel (i dont know how) and we're desperate!!!!!!! xoxo Plz help us u.u
Could you possibly explain the calculation part of the slide bar? It's obvioulsy important for the "offset" formula, so I would just wanting to know what considerations needed to be taken into account in the "=Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1" formula on the Calculation slide. Thanks!
This helped me to create my first KPI Dashboard....Just a small query how can we avoid columns as showing zero value as it takes a set of 10 at a time.
I donot know VBA or macros.
I am struggling on your instructions!1!!!!!!!!!!!!!!!!!!!!!!! It's Not bloody working.......Please advise.
Assign the scroll bar control to a cell right click on it and select format control option. In the dialog box, go to “control” tab and adjust the values as shown below:
scroll-bar-contrl-excel-properties
WHERE DO YOU WRITE THE OFF SET FORMULA??????????
"Finally write OFFSET() formula to display any consecutive 10 values in our scrollable table: OFFSET is used on the dashboard to bring back those 10 lines from the sheet with the raw data that are selected by using the scroll bar. A sample formula is shown here: =OFFSET(Data!E5,Calculation!$D$5,0) where Data!E5 refers to the column containing the required data, Calculation!$d$5 has the current scroll bar value. That is all, you will have a small table that you can use to see all data using scroll"
I think you must be really lazy. Why not download the file and see where everything is. And if you still need help, learn to ask gently. I am sure someone will be able to share their wisdom with you.
Dear Chandoo,
Thanks for such a wonderful insight on Dashboard. It was really great but i would like to know why you havn't put Offset formula same in all the cells of the dashboard table. That is in the first column you have put =OFFSET(Data!D5,Calculation!$D$5,0,1,1) but in the third column =OFFSET(Data!E5,Calculation!$D$5,0). Can you please tell me if i put =OFFSET(Data!E5,Calculation!$D$5,0,1,1) instead will this effect negetively?
hi great post. i have been able to recreate the scroll with my own data. One question why do you calculate the max position with this formula =Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1.
I understand the formula but not sure why you do it this way. Actually i cannot replicate this part and when i debug my formulsa (F9 - another good Chandoo trick) instead of giving me a count as in yours it lists all the numbers like this:{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}-COUNTA(Dashboard!$C$7:$C$13)+1.
Not sure why it does this . I realize this is an older post but if you have suggestions i appreciate it.
[…] is a screen shot of KPI dashboard, in this post you will understand the trick of creating this one in easy […]
Hi Chandoo,
I stumbled upon your blob while doing google search on dashboards. Excellent piece of information I must say. However, I think there is a more efficient way of creating the scrolling dashboard, i.e. by using the array formula. Using your example sheet, I entered the array formula "{=OFFSET(Data!$C$5,Calculation!D5,0,10,7)} and a single formula works just fine instead of entering the formula in all the display cells. I guess that's how offset function was designed to be used.
Regards,
Rajiv
Hi Chandoo,
I stumbled upon your blog while doing a Google search on dashboards. Excellent piece of information I must say. However, I think there is a more efficient way of creating the scrolling dashboard, i.e. by using the array formula. Using your example sheet, I entered the array formula "{=OFFSET(Data!$C$5,Calculation!D5,0,10,7)}" and a single formula worked just fine instead of entering the formula in all the display cells. I guess that's how offset function was designed to be used.
Regards,
Rajiv
@Chandoo - I guess 13-14 years on & this page still lands up in Google searches for dashboarding... what better example would be for timeless/ageless knowledge on Excel? Just that, it might need an update related to Excel 2007 & onward (ribbon) versions...
@Rajiv - Agreed with you 100%, I used this technique (array formula) after playing around for some time with other methods. Just to add my two cents for other & upcoming readers, the range should be selected as per required 'height' of the scrollable information before pressing CSE (Ctrl+Shift+Enter)... i.e. If the dashboard maker wants to display 5 rows of scrollable information, then 5 continuous cells to be selected while entering the OFFSET formula & then press CSE..
Nevertheless, congratulations to you as well, for providing an important improvement which is valid after 7 years & counting...
I can't find the Menu > view > tool bars and select “forms” to see the forms tool bar in Excel 2010. Please help
This does not work in Excel 2010. In Excel 2010, you need to first enable the Developer tool bar. This is done by opening the Excel options (to go to the Options dialogue, click the Office icon on the top left corner of the Excel window and then click the Excel Options button at the bottom). In the Options dialogue, select "Popular" on top left and select the check box for "Show Developer Tab in the Ribbon".
[…] http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ […]
My friend, you are really an amazing guy, please accept my warmest thanks
hi sir,
I have started excel after a long time.
I didn't get a step => how to start like
M sorry! m new
I downloaded and opened the excel workbook, three sheets are there
1-DashBoard, 2-Data, 3-Calculations
when I inseted a new sheet, then I selected ten blank rows for table and I am not able to insert a scroll-bar from > view menu> tool bars and select “forms, I am using Excel-2007. I am not getting the View menu.
Kindly guide, what should I do.
Great chart.. with this data can be viewed very easily!.. thanks you so much
Fantastic-simple & easy to follow steps
Hi Chandoo,
I looked trough your dashboard and it is really impressive. However i am still unable to get the dashboard working like how it is shown in your template.... is there any usage of macros here?
Hi Chandoo,
Great stuff!!!
How can I increase the KPIs to more than 20?
Thanks
Hi,
I am stuck in initial stage itself.. i don't know how to add up thes scroll bar in data sheet
I think this entire article is a great example of when trying to use "straight" excel is a bad idea. By the time you have set up all of these fancy components, you might as well have just created a userform and designed the whole thing in VBA. After you were done, you'd have everything this article accomplished but then a whole lot more....And VBA code is easier to document, provide error handlers, troubleshoot, maintain, and scale.
This is great for a person who doesn't know VBA and has decided they never want to. But it's probably not the best choice for everyone else.
I just set this up and did indeed impress my boss who called it awesome, thank you. 🙂
This may be remedial but I was trying to recreate this from your instructions above and I get stumped by the part that says to create a 10 row table? if the data has many more rows than 10, how do you make the table only 10 rows and then how do you enable it to scroll? You lost me. 🙁
Hi all,
I struggle on the first step of this tutorial. This tutorial assumed you have very good foundation of excel which I don't.
I have figured it out and posted a video for those who are unable to do this tutorial.
http://youtu.be/kDl7NSMsN9M
I hope it helps and this tutorial is awesome! I did not know form control exists.
Hi,
My calculation sheet produces "#DIV/0!" errors from cell G23, downwards and across. There must be something wrong with my 'choose' function, but I can't figure out what the problem is?
I tried to add one more option button to show the original phase without filtering and it works well. Thanks for your coach.
HI Chandoo,
I need to paste linked charts in one location only with the help of click of each option button. i have four charts
[…] Creating KPI Dashboards in Excel [6 part tutorial] […]
[…] Chandoo delivers access to a free Key Performance Indicator (KPI) dashboard plus offers tutorials on implementing and maximizing how to use it in your business to get greater performance from your talent. […]
[…] KPI Dashboard by Chandoo – 6 Part Series. […]
[…] Start using a KPI dashboard in order to keep track of your metrics that can assist you establish criteria for career advancements and bonus for your […]
While doing the same in format control, conrol option is not showing I am using excel 13 what could be the possible reason as I am unable to go ahead.
Where do you sign up for the 25 pages of tips and tricks ebook?
[…] is intermediate to advanced level. I would like you to read Chandoo’s in-depth 6-part tutorial: Creating KPI Dashboards in Excel. By doing that, you’ll be able to build some amazing […]
[…] how to design Excel dashboards, I urge you to read Chandoo’s in-depth 6-part tutorial: Creating KPI Dashboards in Excel. You’ll be able to build some amazing […]
Hello.
I am very much a newbie here...I thought I knew excel but OMG there is soo mucch to learn. sooo... in thr tutorial for creating a scrolling table. I can't seem to be able to insert the scroll bar. When I go to "View" I cannot see toolbars neither can I see "forms" . I am using the 2007 version. please help
The tutorial was very educative and revealing.
thank you
Sorry because of my ignorance, but I could not figure out what kind of function or formula the author have used for the "Actual Start Position" cell in sheet "Calculation". Please Anyone can help me to understand this?
Thank you for sharing your knowledge!
its so useful.
How the data can be displayed into table when i enter new data after serial # 100 in data sheet