This week I have asked a number of owners of notable Non-MVP Excel websites for their input to their favorite Excel Tips, Tricks, Cheats & Hacks.
Notable means that the web site has attracted my attention and made it into my list of Excel Shortcuts in my Firefox browser.
Lets dive in:
001. Percent of True Items in a Pivot Table Field – Doug Glancy
Doug presents a post on how to add a Percentage of True Items in a Pivot Table
You can read the description at Doug’s YourSumbuddy Website
Contribution by: Doug Glancy
Website: YourSumbuddy
002. Fixing Bad Dates – MF Wong (aka Fung)
I regularly encounter dates that have been incorrectly input in Excel. To a regular Excel user, it is really a terrible experience.
Therefore I wrote a post about fixing trouble dates:
https://wmfexcel.com/2014/10/12/fixing-trouble-dates/
Using Text to Columns #4 in the post is my favourite trick to fix this kind of problem
Contribution by: MF Wong (aka Fung)
Website: wmfexcel
003. Using wildcard to reference sheets in formula bar – MF Wong (aka Fung)
Using wildcard to reference sheets in formula bar – a very well hidden magic
This trick, although is not perfect (not dynamic), but is incredibly useful for those who do 3D sum frequently.
https://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/
Contribution by: MF Wong (aka Fung)
Website: wmfexcel
004. Convert PDF to Excel without PDF converter – MF Wong (aka Fung)
Well technically this is not an Excel trick. Nevertheless I find it super relevant in workplace, but not yet commonly discussed on Internet.
https://wmfexcel.com/2015/08/08/convert-pdf-to-excel-without-pdf-converter/
Contribution by: MF Wong (aka Fung)
Website: wmfexcel
005. Conditional Standard Deviation – Andrew Engwirda
Excel does not have a Conditional Standard Deviation, ie: a STDEVIF() function. It’s got other conditional functions like COUNTIF(S), SUMIF(S) and AVERAGEIF(S), but is somewhat lacking when it comes to calculating conditional standard deviation.
I have written a user defined function that adds the functionality of a Stdevif() function.
You can read about it here: http://andrewexcel.blogspot.
Contribution by: Andrew Engwirda
Website: Andrews Excel Tips
006. Text Troubles – Andrew Engwirda
If you work with data from various sources, it’s possible you will come across numbers formatted as Text. Depending on what you plan to do with these numbers, it’s possible that the Text formatting will cause problems.
This post explores how to anlayse and fix some of these issues:
http://andrewexcel.blogspot.
Contribution by: Andrew Engwirda
Website: Andrews Excel Tips
007. VBA Code Indenter – Andrew Engwirda
http://andrewexcel.blogspot.
Contribution by: Andrew Engwirda
Website: Andrews Excel Tips
008. Indenting Code in Cells – Andrew Engwirda
There’s a few addins out there to indent VBA code. I should know this because I made one of them.
But let’s say for argument’s sake that you can’t use any of them. Perhaps you’re using a computer somewhere that won’t allow you to install what you want. You still have access to the VBE, right?
You can do the work in cells on a worksheet, then transfer the results to the VBE
Read about the technique here:
http://andrewexcel.blogspot.
Contribution by: Andrew Engwirda
Website: Andrews Excel Tips
009. Advanced Find & Replace – Andrew Engwirda
Excel’s built-in Find / Find and Replace dialog box is quite useful. Unfortunately you can only find/replace one thing at a time.
(This doesn’t mean you can’t select all cells with the same value at the same time. Just use the Find All button, select one of the results and click Ctrl+A on your keyboard at the same time to select all cells with the same value. The thing is you can only “find” one thing at a time).
Anyway, recently I’ve had to find/replace in lots of cells in various worksheets, and doing the same thing over and over again led me to say “Oh dear, what a bother!”. (Well, perhaps I used stronger language than that)
So I made this.
http://andrewexcel.blogspot.
Contribution by: Andrew Engwirda
Website: Andrews Excel Tips
010. Setup perfect square gridlines in Charts – Frankens Team
This technique combines XY Scatter with Pie charts to have perfect square gridlines.
Perfect square plot area, perfect square gridlines
I haven’t seen this trick published elsewhere, but I really like it because of the simplicity. We used in lot of other chart solutions where the perfect square plot area was key element.
Contribution by: Frankens Team
Website: E90E50
011. Change the Value Fields in Pivot tables using a Slicer – Frankens Team
This technique makes it easier to change the value fields in Pivot tables or Power Pivot using only a special slicer and few rows of VBA.
Use Slicer to select value fields for Pivot table or PowerPivot!
This trick helps my users to easily select the measure they want to analyze in their pivot table.
Contribution by: Frankens Team
Website: E90E50
012. Combine a hyperlink and drop down list – Frankens Team
It is very easy to use a hyperlink to navigate within a workbook.
But if you have lot of worksheets, it may be easier to combine hyperlink and drop down list.
In this post we will show you how easily you can set up this feature!
The trick is in the order of steps.
Hyperlink to a dynamic range – easy navigation
Contribution by: Frankens Team
Website: E90E50
013. Add a % calculated measure without generating blank rows in Pivot tables – Frankens Team
This last technique can be interesting for some PowerPivot users.
It’s a trick how to build up % calculated measures without generating blank rows in the Pivot table.
Solution: avoid using constant value in fraction calculations.
PowerPivot: Constant in calculated field results blank rows
I had not found solution in google. So I started to investigate and found the root of the problem…
Contribution by: Frankens Team
Website: E90E50
014. Calendar Control for all Office Versions inc. Mac – Frankens Team
Contribution by: Frankens Team
Website: E90E50
015. Combine Bubble and xy scatter line chart – Frankens Team
Have you ever wanted to connect the bubbles of a bubble chart with lines?
For example to create kind of a timeline: how the position of a product changes month by month in a Growth/Profit matrix.
You would like to see something like this:
The technique to achieve this is discussed here:
https://sites.google.com/site/e90e50fx/home/combine-bubble-and-xy-scatter-line-chart
Contribution by: Frankens Team
Website: E90E50
016. Useful Worksheet properties in VBA – Paul Kelly
Contribution by: Paul Kelly
Website: ExcelMacroMastery
017. Remove blank rows using special cells – Christos Samaras
Remove blank rows using special cells
Contribution by: Christos Samaras
Website: My Engineering World
018. Using SUMPRODUCT function for numerical integration – Christos Samaras
Contribution by: Christos Samaras
Website: My Engineering World
019. Common auxiliary VBA functions that I use in my projects – Christos Samaras
I use a number of Auxillary Functions to assist me in my Excel and VBA Development
These functions include:
– SheetNameExists
– GetFileNameFromPathNoExtension
– FileExists
– NamedRangeExists
– IsUserFormLoaded
– GetIndexOfArray
– HasBadCharacters
These VBA functions are all demonstrated in the mFunctions Code Module in VBA in the sample file
You can see them all in the attached file: Download Sample File
Contribution by: Christos Samaras
Website: My Engineering World
020. Get data from Tables with VLookup – Christos Samaras
I use VLOOKUP with COLUMN() function and $ symbol for getting data from tables with multiple columns (sometimes exceeding 100 columns).
I have attached a sample, in which you can see what it is:
– A classic VLOOKUP formula;
– In which the $ symbol is used for locking the lookup value (its column actually is locked);
– Instead of column index, the volatile function COLUMN is used;
– This simply requires the Report worksheet columns to contain the same data layout as the Data Source worksheet
Download Sample File
Contribution by: Christos Samaras
Website: My Engineering World
021. Create a QueryTable from an SQL Query – Tom Kacprowicz
This technique creates a QueryTable from an SQL Query (so called MS Query) within your current Excel Workbook. Normally you need to undergo a lengthy 7 step process.
What does it do?
It creates a QueryTable from an SQL Query (so called MS Query) within your current Excel Workbook. Normally you need to undergo a lengthy 7 step process.
4. I recommend adding an Excel Shortcut (Macro Options) or adding it to your Excel Quick Access Toolbar.
4. Provide your Query and Click Ok.
Why I find it a Great Excel/VBA Tip
In just a couple of seconds you can run a SQL SELECT Query on your Data. In case of errors you will see the error message.
However with MS Queries you can build quick recipes for complex data analysis or quick data transformations.
How would you list a distinct list of records from an Excel Table?
Without MS Queries that would be hard (a Pivot Table? An Array Formula? A VBA Macro?), using MS Query it takes 10 seconds (see first example in my Excel file). MS Queries (SELECT) allow grouping, filtering, ordering, distinct.. the list goes on.
You can see how this works in the short video below:
Contribution by: Tom Kacprowicz
Website: The Analyst Cave
Closing
A very special thank-you to the Contributors who contributed to the Excel Tips, Tricks, Cheats & Hacks shown above.
The quality of the posts demonstrates the contributors skill and passion to the use of Excel & VBA.
I hope you get to to revue all the tips and pass comments and appreciation back to the authors as appropriate.
Next week will be the second last post in the Excel Tips, Tricks, Cheats & Hacks series, where I will be asking you for your contributions.
These will be combined with the existing comments from this and the previous 2 posts to make the final final post: Excel Tips, Tricks, Cheats & Hacks – Readers Edition.
As always if you have any Excel Tips, Tricks, Cheats & Hacks you would like to share with us please do so in the comments below:
12 Responses to “Speeding up & Optimizing Excel – Tips for Charting & Formatting [Speedy Spreadsheet Week]”
Usually when I dump data into my files to update values, the formatting sometimes go to all rows or columns. So what I typically will do is go to the last row and then the last column and use Ctrl + Shift + end and then delete the cells highlighted. this will remove all unknown formats in the worksheet. Also, after you have done this, you won't see the benefit until you save the document. Sometimes I even have to close and reopen. The direct sign that this has improved is the size of the scroll bar and range.
I have some comments on a couple of the points.
1. Camera objects
Tip: I use defined names in conjunction with camera tool objects.
Each camera object gets a name like so:
CameraItem01
Referring to: =IF(PicsOn=1,Sheet1!$C$2:$S$5,"")
By setting the PicsOn name to 1, the camera objects become "live", by setting the PicsOn name to 0, they become static. That improves performance enormously.
4: Conditional formatting
Lots of CF rules can slow down your workbook a lot. And it does not show the calc progress a "normal" recalc does on slow workbooks.
5. Format whole columns/rows
as far as I know, there is no problem with formatting entire columns/rows performance-wise, on the contrary, Excel is more efficient when you format an entire column than when you format a couple of 100 rows of a column.
6. Styles.
Here I wholeheartedly disagree. I say: Use styles. And use them religously.
I mean: if you have applied a (custom) style and you need to change a small piece of formatting to make that one cell look right, force yourself to create a new style just for that cell. It forces you to really think about your spreadsheet design and try and streamline it. It also makes it much, much easier to change your sheet's appearance later on. See http://www.jkp-ads.com/articles/styles00.asp
Very good insights Jan..
Camera objects: I often use similar technique to turn off images in my dashboards.
Formats: Thanks for clearing this. Do you think formatting larger ranges has any impact on macro speeds or it does not matter?
Styles: Thanks for telling us about this. As I mentioned, I am not sure about the styles, but I am under the impressions that excessive use of styles can bloat the file size.
@Chandoo:
If you stick to formatting entire rows/columns I don't expect macro speed is affected. Better: try it!
If you use styles properly AND as a replacement of ad-hoc cell formatting, I expect you'll see that the file actually is smaller in size.
This is because the cells now only have a reference to a single style instead of a reference to a custom cell formatting style.
Many cell formatting combinations get created if you format your cells in an ad-hoc manner, which was responsible for the dreaded "Too many different cell formats" error in Excel 2003 and older. Excel 2007 and 2010 have a higher limit there, but it does slow down your file with many of them.
Style bloat in my point of view is what you get by copying and pasting a lot from various other files and thus get Normal 1, Normal 1 1, Normal 1 1 1, ... I have seen workbooks with as many as 6000 styles, all caused by copying and pasting from various differently formatted workbooks.
Excel 2007 and 2010 have fixed a number of issues regarding copying of styles, but for workbooks with a long editing history, the trouble is already in the workbooks.
Cant emphasise the importance of reducing the amount of formatting in a workbook - this has a suprising impact on workbook size. I've always kept to one font, and no more than three colours - this has worked well for me. Keeping things clean and simple should be the motto when designing any type of report/dashboard that is going to be distributed around the organisation.
You can also save a few MB's by saving as an xlsb file.
Has anyone else mentioned that only the first item in the "more ..." section is hyperlinked.
Prem, have you confirmed by trial that XLSB file size is smaller than same XLSX file? Sorry, I just tried it with a small, simple XLSM file. I was surprised to see you are correct. File went from 40kb to 37kb. I thought that the compression of the new file would make the new file smaller.
@Ron
All Excel files have a minimum overhead that they have to include which is around 8KB, just to store a simple number or letter.
So with a small file of 40KB you will not see a huge improvement in file size
With files greater than 10MB you will see large improvements in size.
The compression gained also depends on what the contents of the file include. That is straight numbers, text and formulas can be greatly compressed whereas files that contain a lot of objects especially pictures gain very little from using *.xlsb files.
@Ron.. the other articles are yet to be published. All the links will be updated by Tuesday (27th March).
Hi,
I have a need for x,y scatter chart to have arround 30 data series.
like this:
http://i65.tinypic.com/jra8lc.jpg
Also I have multiple of such charts in one excel file.
Is there any way to make excel faster, because it is irritatingly slow?
(though my PC config. is quite on the level)
Thanks in advance!!!
@Mil
30 series won't be the issue
It is the number of points in the series
Also remove all fancy modifications, like shadows, fancy fills etc
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after
@Hui
I've already removed all fancy mod. The problem is there are also a lot of data points in one series.
Thanks for the advice!
@Mil
Do you really need every data point ?
Where is the chart being presented Screen or Report
On a screen you are unlikely to use more than 800 pixels for the chart area
So using any more than about 250 points is not adding values
On an A4 chart in landscape lets say the chart area is 6" long and at 300dpi that is 2000 pixels
Once again using more than 800-1000 points will not add any value
I have seen charts with 30,000+ points and when this is explained and a work around shown people appreciate the speed up
For a work around try setting up an area where you select say every x'th point using an Offset or Index Function
Then plot that data
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after