As part of my new gig, I’m delivering excel classes to the wider business department. It’s amazing the differing levels of exposure some staff have to excel and data as a whole. Things as simple as Ctrl+C have passed some staff by, whilst other staff members are teaching me things. One of the simplest, yet palm-to-face inducing thing is “text to columns”. I wrote a macro to split out some columns data! (it’s the .split function kids)
The moral of the story – CLICK EVERY BUTTON, even the big red one.
It’s quite intuitive:
- Select your column and how you’d like to split the text – by fixed width that you specify or by delimiter.
- You can select multiple delimiters too.
- Then select a cell you want the data to end up in (careful not to overwrite your existing data!)
Following on from my discussions of the design ideas of Edward Tufte and before my discussion on mathematics, maths and graphs I give you my 5 top tips for excellent graphs!
1. Decide your Statement: Absolutely essential! What is it you want to say about the data set you are discussing? Although graphs can show a huge amount of detail and provide fantastic insights into the behaviours being investigated, it is essential when presenting a graph to have one idea of the statement you would like your audience to take away from it. You might find they take away more, but they have to take away at least that argument. Apply this statement to your graph and make sure that this is what it says, remove anything in the graph that distracts from it.
2. Could a table represent the data better?: Why are you including this graph? Just because you have a nice data set and think a bar chart would break things up? Because you want to show off the fact that you have worked out how to do histograms?! NO! STOP! Your job as a data scientist is not just to produce indefinite numbers of graphs so your inner quota is fulfilled. Your job is to analyse the data on hand and allow your audience to fully understand your work. A table may well be better for this than a graph.
3. Remove chart junk: Remove all those grid-lines! why do you have all those colours? Remove background colours, data point colour variations, data point shape variations. Remove data labels! Simplify your legend!
4. 3D Visualisations: 3D visualisations should only be used to represent things that are actually 3D! If you’re showing an MRI of a patient or temperature fluctuations throughout your manifold go ahead. But 3D visualisations should not be used to make things look cool, keep your audience awake or fill more space on your page.
5.Colours: The most effective way to draw the eye to the story graphs tell is to use colour distinctions. Use of strong bold colours against a background of muted colours makes data stand out. Too many colours will distract the reader and the graph will become unreadable. For data sets of trials where one variable is changed, a change in hue of one main colour can aid in describing the relationship between the variable and the output.
A large amount of work I do is in excel, be it simple tables, pivot reports or VBA Macros. One hurdle I’ve been facing recently is how to overcome the problem created when excel is running a macro and you have other work to be done in excel.
Say your macro is used to run through a big loop and whilst you’ve improved it for speed and simplicity (see my Top Tips for VBA Run Speed), it is doing a big job and big jobs take time! This is often a brilliant excuse to go make yourself a cuppa and check up on the water cooler gossip. Regular breaks make you work better and stop you turning into a Square Eyed Zombie. However, I have cracked a way to work with excel whilst it’s working on your number crunching.
1. BEFORE you run your macro, open up a new window of Excel. This is not a new document, but a whole new window. Do this by: Right clicking on the excel icon, (which I’m sure you have pinned to your taskbar if you’re a geek like me) and click the icon marked “Microsoft Excel”. Check this has happened properly by trying to view two different documents in the two windows (you should have two unique sets of menus)
2. Run your macro in the first window
3. Work as normal in the second window!
These two windows aren’t linked so when your macro is finished you’ll have some limits when working with both windows (e.g. coping and pasting will only paste values and not formulas).
Five top tips for improving your VBA speed
1. Write good code: Your script is only going to be as fast as it’s weekest link, create good habits by keeping your code neat, consice and your macro will run fast
2.Screen Updating: Turn off screen updating whilst you work – Added benifit that you wont have a flashing screen as documents are open and closed
3.Automatic Calculations: Turn off these and it’ll stop your formulas recalculating every single time you add to your workbook
4.Enable Events: This is a bit obscure, but it has saved more than one macro from the recycling bin. This is a more complex version of automatic calculations, which might cause you problems, but really improves speed
5. Active Workbooks: Stop activating workbooks, it’s not necessary and will slow your code down. Instead define your workbooks or ranges or what have you and reference the locations.