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.
66 Responses to “Excel 2007 Review – 10 things that WOWed me”
Hey, better late than never!
It's very easy to trash Excel 2007, but it definitely has a few good things going for it. Consider it the beta for for Excel 2009 (or whatever it's called). Hopefully, the major problems will be fixed and other usability tweaks will be incorporated. I don't expect too many major new features.
Use it exclusively for 4-5 months, and Excel 2003 will seem like an ugly old dinosaur. At least that's how it went for me.
To paraphrase John's esteemed opinion, it's very easy to be distracted by the updated visual appearance of Excel 2007. And I will not deny that Excel 2007 does have some good features going for it. However, the drawbacks far outweigh any of these improvements. I have really tried very hard to like Excel 2007, starting in 2005 in the late alpha testing stages, and I can say that experienced hard-core users will find themselves less productive in Excel 2007, even after they know where their favorite commands have been moved to.
The ribbon puts many commands out of sight on the inactive tabs. In all honesty it seems like the new charting machine was never completed. The dialogs which have been redesigned, particularly the chart and shape formatting ones, cannot be used as efficiently as the old dialogs. Moving some options from chart formatting dialogs to charting contextual tabs makes them harder to find and harder to use. If you have any charts with more than a few thousand points, which is not a large total by any means, and Excel bogs down whenever it decides to refresh the charts.
I've blogged about several aspects of Excel 2007:
A Belated Review of Excel 2007
Changes to Charting in Excel 2007
What happened to my favorite Excel 2003 Chart feature?
Excel 2007 Chart Performance - Revisited
Error Bars in Excel 2007
Wow, Excel '07 looks much better than I thought it would. For some reason I had this picture in my head of it being this ugly monstrosity. Now if only my company would upgrade. We just went from 2000 to 2003 about a year ago, so I doubt it's going to happen soon . . . .
@ Chandoo :
Cud u pls inform the merits and demerits of "OpenOffice.org Calc" w.r.t. excel.
@John: That is what I am planning to do. Thanks.
@Jon: Thanks for the pointers. As I mentioned this post is first look review only and I might as well discover some problems with Excel 2007 ( I am hoping not though 😀 )
@Rich: It is a surprise for me too, but the overall feel of excel 2007 is far better than expected. Try it sometime and find your reasons to like / test it.
@Ketan: sure, I have already installed Open Office Calc and been thinking about a review for a while. Will do it in the next few weeks.
I also liked the charting features of 2007, initially. But, I have to work with others who have earlier versions of Excel. There is a huge problem in compatibility: a chart drawn in 2007 will not display fully in earlier versions: the left or right edges of the chart will be partly cropped, making them unreadable. They have to recreate the chart with their older versions.
There is also a danger that if you install 2007 on top of 2003 or earlier versions, your Windows installation will be corrupted, and you will never be able to access your older Excel version with OLE ever again (I have had to reformat one of my Windows machines to get rid of 2007). My experience has been that Office 2007 is one of the nastiest viruses out there: once you install it, all registry references to other versions of Office are eliminated, and you will not be able to regain full use of your older Office versions, even if you uninstall 2007 and reinstall the older Office version.
I hope you have better luck than I did with the 2007 install, especially if you use OLE. If you can get the 2 versions to co-exist, write about how you did it.
Chandoo, Excel 2007 is a dumb blonde. You saw the blonde, now prepare for the dumb.
Rene -
I have heard from many users who have installed multiple versions of Office on their computers with no problems (including one person with Excel 95, 97, 2000, 2002, 2003, and 2007). The trick they say is to install in order from the oldest to the newest, in different directories (e.g., C:\Program Files\Microsoft Office 97, \Microsoft Office 2000, etc.).
Perhaps it's my own usage patterns, but I have in fact had issues with multiple versions. I suspect some of the problems arise from the shared resources, especially for me the user defined chart gallery. Another source of problems which I think relates to your issue of 2007 settings persisting past uninstallation, is registry settings. I also don't think you can reliably control which version of Office is summoned for OLE interactions, just as you can't relaibly control which version is opened when you launch a file from Explorer.
Lately I'm seeing some issues with userforms on computers with fresh installs of 2007 and with Excel 2003 freshly updated to SP3. It is a sporadic problem, most people don't experience it, some who do can be relieved with simple remedies, and some defy all countermeasures. Unfortunately the error message is "Unspecified Error" (no joke!) and searching Google brings up earlier unrelated instances of this error.
@Rene: I came across the chart compatibility issues as we have partially migrated to excel 2007 in office and most of us are running that compatibility mode to avoid surprises.
Thanks for the warning on co-existence of 2003 and 2007. I will write another post if the installation is successful.
@Jorge: 😀
@Jon: thanks for explaining this and few other glitches. I will probably do more research before installing 2007 on top of 2003 as any disruption to 2003 would be very bad for me.
"It would have been excellent if MS had provided an option to switch to classic menu navigation."
Chandoo- They Have.... You just have to turn it ON 🙂 ....and we should be thankful that they are allowing us to customize the Ribbon to display the Classic UI...
http://i41.tinypic.com/20jk01t.jpg
After reading several reviews of version 2007. I am still waiting to upgrade, my priority is productivity.
Of course there are things in the 2003 that needed improvement, but
I will consider the upgrade when I can customize the UI and when some of the 2003 lost features are added to the new Excel version.
@Gabriela Cerra: I agree. Sometimes I use Excel 2007 on my father's PC for 4 months and still I can't get used to it.
The only thing I really appreciate is "3. Status bar now shows average and count as well" mentioned by Chandoo. I really miss this feature in Excel 2003, which I use most of the time. :'-(
I'm not sure, if "most of the dialogs are same". I find them too big and work with them is less effective.
Even though the conditional formatting is pretty now, it is buggy too. I rely heavily on conditional formatting, and I dislike the globalness of it in 2007. If I just want to remove formatting on a specific area, it is confusing to do so, and sometimes it applies the conditions to cells that I did not intend. The relative references get messy as well (if you want the cells in column B to change color if they match their corresponding cell in column A, and then add then you add data and apply it to more rows, you end up with too many "Applies to" references after a while, hard to clean up).
@Sam: Thank you. I am aware of a VBA based tweak for this (http://blog.livedoor.jp/andrewe/archives/50820196.html) but I dont know if there is a straight forward way to enable old style menus in 2007. Do you know any easy way to do this ? please ... please tell me 🙂
@Gabriela: I am not sure how much MS would allow users to roll back classic UI, because I heard they have added ribbon UI to MS paint and wordpad (the free stuff that comes with windows) in their latest beta version of windows.
@Struzak: "I find them too big and work with them is less effective.", you are right, the dialogs are unusually large. May be MS is hoping to add few more additional features in the coming versions and getting us ready for bigger dialogs.
@Spashi: That is some what disturbing to know. Even I use Cond. formatting alot.
Let me do some more research on this so that I wont accidentally add conditional formatting in a wrong way and send the file to a customer.
The tweaks that allow us to emulate the classic menus in the ribbon are not very elegant. In fact, the ribbon is pretty easy to control, once you get over the initial fear of the unknown. It's way easier to customize using XML than it is to use within the user interface.
I would expect MS to be strongly reluctant to roll back to an earlier interface (menus and toolbars), despite their effectiveness and the strong backlash against the ribbon. They went through months of blogging and explaining, justifying how they used incomplete automated user feedback and faulty logic to develop the new system. Apparently we users are too stupid to be trusted with something as flexible and customizable as the toolbars and menus, and we will be happier and more productive with a system that's much less flexible and almost completely uncustomizable. Um, or at least that's what we were told.
The large new dialogs for the charts (for example) seem to have loads of empty space which could be put to more effective use. To format lines and markers for a chart series in Excel 2003, I need visit only a single tab of a dialog, whereas in Excel 2007, I must visit up to six tabs. If I need something special from one of these tabs, like 3D effect or something equally gaudy, the options appear right on the same tab. The equivalent in 2003 would be shown on a child dialog, and only when the user had called it. But in 2007 they shun child dialogs, except where they make life less efficient: how else to explain the need to pop up a dialog to select data ranges for chart series, custom error bars, etc.? These worked rather nicely on the same tab in 2003.
Looks like I'm ranting. Again. But these are the things that have caused me to feel trapped in an inefficient user environment in 2007. The feeling of constraint makes it impossible to enjoy the real improvements that were made.
I don't expect to roll back the classic UI, only some EASY customization, few lost things like a floating toolbar and not to do 6 clicks instead of one.
@Jon: "to format lines and markers for a chart series in Excel 2003, I need visit only a single tab of a dialog, whereas in Excel 2007, I must visit up to six tabs." I totally agree with you, I couldnt understand the rationale behind this either. They have separated things in to multiple tabs when they were working excellent in one place.
Chandoo:
I have suspected (without any real information) that the designers and programmers of the new charting user interface did not actually use this interface in daily battle. I only make small utilities, admitteedly, nothing as massive as part of an Excel-size application, but they are easy to use because I designed them so I could do things more easily.
NO VBA - JUST XML -
1st Hide all built in Tabs except the Addins Tab
2 Create a New Tab called - Menus
On this you can put one rows of menus + 2 Rows of buttons... the ribbon just allows for 3 - another stupid limitation
sam
sir i requested that Please sent me excel formula.
thank you.
your respectfully Ramesh Kumar tripathi .from opk eservices.
@Ramesh:
Can you please explain what formulas you were looking for?
Hi there,
I am trying to figure out if there is a way to get other color schemes for Office 2007. I know there are Black, Blue and Silver included in the pack, but there must be a way to edit them. My boss is having difficulty in seeing the difference between active tabs and inactive tabs in the Blue theme, and the Black and Silver are not really much better. Any ideas??
Thanks
[...] In earlier versions of Excel you can only define max. of 3 conditions. This is no longer true if you are using Excel 2007 (read our review of excel 2007) [...]
When opening an Excel 2007 file in 2003, the colors change, even if I save it in 2003 format, and even if I use the same RGB colors in 2007 as I use in 2003. How can I keep the same colors when opening in different versions?
When opening an Excel 2007 file in 2003, the colors of filled cells change, even if I save it in 2003 format. How can I keep the same colors when opening files in different versions?
Barry -
I think the only reliable way to do this is to use white and black, and maybe primary colors. There is minimal compatibility between color models between Classic and New Excel.
Stephanie -
These are the only schemes available, and I don't think they can be modified. Microsoft seems not to allow for 40+ year old eyes in their interfaces. There is a Windows option for enhanced contrast, I think buried in the display settings, but it makes everything else stark and ugly.
I don't mind the 2007 ribbon except for two reasons:
- I have certain tools I use and when I consistantly use a function, I want it available on a customizable bar.
- Tools I never or rarely use, I take off the bar.
It's that simple. I've gone back to Excel 2003 and have started using Open Office as well. The future for Excel, on the current path is dismal for the experienced user, IMO.
I hated the new ribbon when I first saw it on beta versions in 2006, after using 2007 for the last 2 1/2 years It grows on me, while some options I cant find, overall I find it faster to use.
The new tables and improved pivot tables have saved me hours of work!
Perhaps microsoft has spent some of the time on office 2010 in speeding it up!
And if you would like my 2cents on a feature for the next version!
The ability to define a repeated part of a formula IE
=if(sum(range)>0,sum(range),0 (simple example but sometimes the parts can be big)
it would be nice to do
=define(Form1,"sum(range)") IF(form1,Form1,0)
or similar to cut down on repetition!
I totally agree with Jon Peltier. I love Excel 2003 and hate 2007. All my work has been affected by installing 2007. Most good reviews regarding excel 2007 are about its looks but productivity has been definetly affected. I regret upgrading from 2003.
User interface is just one of productivity to consider. What about after the weeks you took to re-learn the new look-and-feel?
Microsoft Excel 2007 is a major rewrite so it is justified to wait a lot from it.
It really brings great benefits, to name just a few:
1. Forget inserting a column/row to exclude adjacent data in Autofilter with new Excel Table
2. Filter discrete items. Use check boxes to select or deselect items
3. Don’t guess anymore when picking arguments (blocked cells or unknown cells because a blocked column header due to a flooding formula bar). Formula bar content is automatically wrapped
4. Delete multiple names at once
5. Dual-processors and multithreaded chipsets to perform faster calculations in large, formula-intensive spreadsheets
6. Focus on data analysis as you add data. Excel Table AutoExpansion
7. Delete duplicates intuitively and by field
8. Better charting and Pivot Table experience
9. And more...
Read this article about what makes Excel 2007 better than Excel 2003: http://www.excel-spreadsheet-authors.com/excel-2007.html
John -
Your points 1 and 6 re Tables in 2007 were true of Lists in 2003. Lists already had much of the capabilities of Tables, but they were a well kept secret. Lists were the killer feature which got me to upgrade from 2000 to 2003.
Half of your point 8 is false. The charting experience has been mostly much less pleasant. The default formatting is less ugly (note I didn't say "not ugly"), and the process of formatting a chart has become tedious and obscure.
The items you mention which are new have not been sufficient to entice me to upgrade for my own work. I will have to admit that 2007 has given me a lot of conversion work, but much of this work has been unpleasant, both for me and for the clients who had to relearn how to use Excel.
[...] Excel 2007 Review – 10 things better and cooler in excel 2007 Excel 2007 Productivity Tips & Tricks Excel Pivot Tables – Tutorial & Tricks Excel Conditional Formatting – 5 Must Know Tricks [...]
One more point for Excel 2007 worth mention is the no. of rows;
from 65536 to 1048576. Phew! That's great!
[...] Personally I think ribbon is a good way to explore an application. I have gotten used to it since I tested excel 2007 for first time. Now, during the rare occasions I work on excel 2003, I feel strange navigating [...]
"One more point for Excel 2007 worth mention is the no. of rows;
from 65536 to 1048576. Phew! That’s great!"
Hah, wow.. how did it take soo long for this comment to appear, IMO this IS the reason to get Excel 2007.
I have been using 2007 for a couple months now and I am absolutey disgusted by it. Clearly Microsoft wanted to create a program that was beautiful for novice users, however, as someone who knew Excel inside out I find the new appearance and layout to be very inefficient. The way we describe it around our office is 2003 was the girl you wanted to marry, 2007 is a one night stand.
"2003 was the girl you wanted to marry, 2007 is a one night stand"
I think 2007 is what Rodney Dangerfield had in mind when he coined the phrase "two-bagger".
From google:
Two-bagger: An extremely unattractive person (usually female), the insinuation being that one bag covering her face would not be adequate. Within this joke, the second bag is often understood to cover the male's head in case the female's bag were to fall off
Jon.. are you sure you have a bag on? 😛
On a more serious note, I found Excel 2007 pretty ok. Infact I have been using it since new year 2009 and so far I havent had any major complaints about it. It is different w/ people like Jon who make commercial tools based out of excel as several things have changed in 2007.
I am a power user of Excel and have been trying for a year to make XL2007 work as effectively as 2003. Definitely the million rows, the PDF add-on, and expanded chart types help. But the problems for serious users like me are major.
- I get frequent warnings that "data may have been lost or corrrupted."
- Almost every workbook has "minor loss of fidelity."
- Date records in columns that are used as chart X axes get replaced with a weird string.
- Many keyboard shortcuts like Alt E A (Edit Select All) do not work or involve an extra step. Worse, here Excel pretends to recognize what you want but does not do it.
- Some VBA macros would not work until I declared all variable types.
- XL'07 reports "invalid names" were changed to "#REF!" but then I could not find any such.
- Prompt for passwords suddenly stops appearing; workbook just opens.
- PDF writer within Excel (via MS Add-In) worked fine at first then started to produce mostly gibberish; now useless.
- As some mentioned above, the tooolbars are much less flexible and useful than before (I am surprised the backlash has not been more severe on this); that is a major step backward.
- One critical workboook suddely started to take five times longer to save. Can't tell why.
- Print Preview button stopped working altogether.
I have nothing agains change. But this has been a real productivity killer. Takes an hour sometimes to do what used to take minutes. Again, I am surprised more people are not up in arms at the changes.
Fear not Berferd
A lot of the issues you discussed have been addressed in 2010
But I still agree with your opening line that 2007 and even 2010 is not as effective as 2003
My old Laptop and I have been using another office laptop with XL 2007. What a disaster this software is - sure there is a learning curve with new things - but as many observe even after you figure out by trial and error where the functions are hidden, it takes more clicks to get to the function you need than the old version - How about a classic toobar option - why should everyone's productivity crash so that Microsoft can roll out software that benefits only themselves with new sales and a comparatively small number of users.
As a long time user of Excel (since version 2) I first tried 2007 as a CTP version at first I thought the ribbon was designed for new users and was an annoyance. After daily use of it for the last 3+years I wouldnt go back (I had to use Excel2002 recently for 6 months).
2010 which I am using on my laptop now has resolved some of the niggles I had, now I can edit the ribbon quickly and create tabs which suit me! The only complaint I have about excel is the fact that Visual Basic is not improving, it would be nice to work in VBA in a more visual studio like environment (yes i know about VSTO)!
@John... Most of the experienced xl 2003 users have similar comments. I think MS is addressing a few with xl 2010. But Ribbon seems to take a firm position in future plans of MS Office so you might as well get on with it and try to benefit from the implementation...
@Squiggler... I have been using xl 2007 at work and home for last year and half and I must say I love the UI now. For some customer projects I use xl 2003 and it does feel a bit difficult now to search in excel 2003 menus...
Even allowing for the novelty factor I find that the increase in key strokes & the inability to have favourite tools annoying.
I frequently get “minor loss of fidelity” messages.
Tabs do not always copy.
Sometimes when one presses Shift+2 instead of getting " which you need for =text(D4,"0000") you get @ =text(D4,@0000@) . The only solution to the later being to save the file. close it, close Excel, close machine 7 reboot. It is simpler to go back to Excel 2003.
It is true 2003 was the girl you wanted to marry & 2007 was the dumb bleached blonde, who makes one want to say "Just going to the bar/loo" & then make a run for it.
I'm hoping 2010 will get back alot of the customisation but I'm fearful the people who give it good reviews gave 2007 good reviews too.
Hey ho, not all change is progress
I am firmly in the favoring Excel 2003 camp. Agree with the comments that talk about how the changes are visual, but productivity is actually impaired. Also found what seems to be a 2003/2007 compatibility issue. I have a spreadsheet created in 2003. Normally it has the "freeze panes" active. When I bring it into 2007 (compatibility mode & converted both). I can't unfreeze the panes. When I went back to unfreeze panes in 2003 then bring it back, I couldn't freeze the panes.
Go figure.
I've been using 2007 for 6 months now and it is disgusting how awful it is - even the Help section is an ineffective mess. Everytime I need to do anything slightly signficant, I have to google for Help and troll various websites. It's two steps forward - about ten steps backwards. My productivity with the application is down about 50%.
Advantage of Columns - 16,384 and Rows - 1,048,576 raised from 256-C, 65536-R.
Worth for large range user.
excel2007 good for use but when i seek this new version come it s boundries are large
Is password protecting a document (or spreadsheet) more secure in 2007 than 2003? Or is it the same?
I have a couple of excel files that I password protect. The files are in compatibility mode (2003 format). Since the data in those files is very critical I want to be absolutely sure if it is safe to be kept in there. Else I will start hunting for a more suitable way to hold sensitive data.
Awaiting your thoughts. Thanks.
Password protection was one area improved upon in Excel 2007
I'm note sure about when your working in compatibility mode though
If you want to be secure, definitely change to Excel 2007 file formats
I agree with all the comments about productivity. I use a lot of shortcuts in 2003 version and even though I know most of them by memory, once in while I need to refresh my memory and look into the menus for they right keys. Since 2007/2010 don’t have the same menu, now it is impossible for me to know a short-cut if I don’t remember the exact keys or I need to learn the new keys which they are longer paths.
In addition pivot tables /filters I used shortcuts to make selections in 2003, can’t do that in 2007. I heat it because productivity decreased for me.
The thing which wowed me was colour filtering & Sorting
Love how all the "wow" is eye-candy crap.
Sorry, but I finally let the IT department upgrade to 2007 from 2003 (yeah, I know how old all this is) and apart from the (expected) difficulties with the "intuitive" UI redesign (which literally takes me twice as long to find and do anything) I've hit a HUGE unexpected roadblock...
The 20MB database/iteration-machine I've been maintaining for the last three years has gone from taking 5-10 minutes to run a number-crunching session to, wait for it, 90 minutes!!!
I was able to reduce it to 50 mins by changing to xlsm format, but this is still around ten times as long as before. Unbelievable!
This is so bad, that I'm probably going to have to ask IT to install a separate copy of Excel2003 (or maybe run it in a VM or something) just to carry out this task.
well, no offense, but excel 2007 sucks. 2003 is much more convenience when it comes to data processing and graphing. Can I install excel 2003 in window 7?
@Anson
That is a common reaction of people who have used Excel pre 2007 when they change to 2007.
My advise is to skip 2007 and go straight to 2010.
Yes it has the same interface, which trust me once your used to, is actually easier to navigate, but it has the problems that 2007 has mostly resolved as well as all the niceties Chandoo has described above included.
Why, if all the "wow" seems to be about graphics and formatting, is it now so bloody hard to make a good graph?!? Why can't I use cursor keys to move text boxes? Why can't I copy/paste information in graph data selection? And the real killer: why can't I use cursor keys to edit data ranges for graph input?!?!?
I have submitted a request to downgrade to 2003.
Hi;
I have a problem with "New Comment" in Review tab, in Excel 2007.
It is inactive and I couldn't find how to activate it.
Any comments?
@Shandiz,
Check to see if your worksheet / workbook has any Protection... if yes you would need to remove that first before being able to add comments.
~VijaySharma
Hadn't done a lot of serious database connections and queries with pivot tables and graphs for a few years so was very shocked today to see that office 2007 is incapable of correctly making a simple graph.
I'm done with this and don't even feel like an IT pro should be re-educated every single time MS decides to release a so called new and improved version full of bugs.
Going back to 2003 and will leave the new 2010 version I have lying here for what it is.
Excel 2007 is an attempt to dumb-down excel for beginners, but infuriate power-users. 1) macro-recorder skips over various steps it used to record. It used to be a very powerful tool to kit-bash together VBA code very quickly. Now, you're left filling in all kinds of gaps from whatever it skipped over. EG: Insert a chart or picture into a worksheet (not as a sheet, but as an object into a sheet). Macro record you deleting that chart/picture. Look at what it record... nothing. This is sloppy. 2) Due to ribbon, they replaced the easy-to-use custom toolbar creation and functionality with bend-over-backwards XML coding and other junk you have to do just to get custom buttons up and running. It's ridiculously user-unfriendly, and a slap in the face to power-users stuck maintaining old toolbars from previous versions. 3) They reorganized some things for the sake or reorganizing. There's no longer "Tools", which was basically a quarantine of advanced functionality for power users. Instead, they shuffled all of those things around to other locations. EG: instead of Tools > Macros, you now have View > Macros. However, they got rid of some of the most useful tools, like the Print Preview button. Look under Page Layout ... no Print Preview. Views? Nope. 4) Office Help flat out sucks. In offline mode, it's about as useful as a tick on a dog's ass, b/c it only glances over the most basic of functions. You can hook to online mode for more robust help, but all that does is put you into "Bing" search engine mode. It's as if you opened up Google to ask your question... and when I ask quesitons and some of the top "answers" that come back are NOT help files written explicityly by microsoft, but instead independant forums where people are scratching their heads asking how to do something, and you have to read 20 people discuss what the problem is, is it this, is it that, will this help, maybe if you try this... Did MS fire their technical writers? The help files from Excel 95 & 97 were amazingly useful and detailed. Excel 2000 saw the quality of help files sliding, and I almost always used Google first. Now... they basically don't have a help file, they just make you go right to "Bing" if you want some answers. In my opinion, they basically charged customers to reinvent a wheel, break things as they went, and did away with any useful help whatsoever, leaving the customer left searching the internet for valid help. This is ridiculously sloppy coming from a multi-BILLION dollar software company that's been churning out the same office software suite for 20 YEARS. Calling 1 step forward and 2 steps backward "progress" is ridiculous, and I think you're being overly generous and sympathetic to MS by doing so. If you want to say you like this new product...fine. Everyone's entitled to an opinion. But, when I upgrade to a new product and LOSE functionality, that is NOT and upgrade, and it's insulting they charged people money to downgrade like this.
I guess as a side-note, what really soured my opinon of 2007 from the start was how I converted some 2003 reports into 2007 format in order to take advantage of 2007's file compression. However, opening one report a couple of times, it would blow up, then Excel would say "hey, I can recover this for you...would you like me to?" If I click "no", it won't open at all. If I click "yes", it basically wipes out ALL of the formuals in my workbook (and we're talking a lot of formulas!), and replaces them with hard values. I got to spend 1/2 my day rebuilding the formulas in the workbook. Saved it back to 2003 format, and left it alone. I tried using 2007 format a few more times, and had the same periodic blow-ups / recovers that would hose up my work. Like I said, this is not progress. It's infuriating losing work, b/c of the tool you're using. It's the 21st century. MS should be doing better than this... WAY better, seeing as they seem to be regressing instead of progressing ever since 2000.
Graphing is significantly more cumbersome in 2007 than 2003. th e"Wizard" stepped through the build of the graph, and editing it after was also more straight-forward.
Considering usability, you would think that 2007 was the predecessor to 2003 and not the reverse.
If you use graphing whatsoever, I would not upgrade.
Agree totally, Greg. I've been coping with 2007 for a year or so now so I can confirm what you say and add to what I mentioned a few months ago (further up the thread).
Graphs are harder to tune, they're uglier (wasn't the point to make them "prettier"?!), they're less clear if you're plotting serious science instead of acting the powerpoint jockey (i.e. more than a dozen points), and bizarrely I found out its the graphs which are slowing everything down in my large database. I can *SEE THE REDRAWING* when I switch to a graph after changing the data!!!
I've had to completely rebuild and reprogram my database in order to bring the calculation speed back to something like it used to be in 2003, and I've spent several months learning tricks and workarounds to be able to deal with all of 2007's many quirks. I mentioned the cursor key thing in my previous comment... that might be some kind of installation problem since no-one else mentions it (it's a work PC so I can't re-install it myself) but in my case the cursor keys *always* move the cell on the sheet when I'm trying to edit in dialogue boxes. That means using the mouse to place the cursor at various points in the text when selecting graph ranges, defining conditional formatting, etc. The moment I touch the cursor all my editing is wiped out! Also, CTRL-V doesn't work in these cases. It's the most incredibly annoying PitA, and I've had to train my brain to reach for the mouse to click, edit, move, click, edit, right-click, paste, etc. instead of my instinctive cursor left/right, CTRL-C, CTRL-V - which used to be at least twice as fast.
Oh yeah - and bugs. I'm continually finding bugs... even now when it's a five-year-old product!
sorry if this was already posted somewhere else, but I found this Tool to be a great resource when converting from 2003 to 2007.
Graphically shows exactly where to find a command using Ribbon - just need to select using old (2003) method thru Excel Menus.
http://office.microsoft.com/en-us/excel-help/interactive-excel-2003-to-excel-2007-command-reference-guide-HA010149151.aspx