Microsoft Office 2007 Excel Customised Toolbars and Optimisation

PushON | July 27th 2010

Some people, geeky people I’ll admit, have a real problem with the addition of Microsoft’s Fluent User Interface in the Office 2007 package. Now personally I got used to it in Word and I actually quite like it in Powerpoint, but in Excel it simply hides everything and makes it incredibly difficult to use efficiently. Therefore this guide takes you through customising your Excel toolbars and using relevant hotkeys to optimise your Excel-based ability.

Microsoft’s Fluent User Interface in the Office 2007, as it appear in Excel

Excel is a massive piece of software that includes a multitude of functions; and people use it for a wide variety of purposes. Because of this, everyone will have a slightly different set of ‘everday functions’. I will be showing you the set up I have as a Junior Web Analyst and an SEO, the features which I use most often and therefore add to my Quick Access Toolbar and will then describe why I find these useful additions. The features you add to your Quick Access Toolbar may differ greatly, but this guide should still be useful to you…
First things first, let’s get rid of the horrible Fluent User Interface ‘Ribbon’, do this by right-clicking in the header and selecting ‘Minimize the Ribbon’.

Minimize the Microsoft’s Fluent User Interface ‘Ribbon’ for the first step of customising your toolbar
It should now look like this (nice and neat):

Once the Microsoft Fluent User Interface ‘Ribbon’ is minimized your toolbar should look like this...

Now by right-clicking the header again and select ‘Customize Quick Access Toolbar’:
By selecting this function you can start to customise our Quick Access Toolbar and optimise your Excel
In the new window select the drop-down to “Choose commands from:” ‘All Commands’:

By selecting'all commands' you can choose from any command that you use on a regular basis to add to your Quick Access Toolbar
From here you can add any of the commands to store them in your new toolbar, below is a list and description of the commands that I have on my toolbar:

  • Align Text Left – Simple but very useful for formatting data.
  • All Borders – An easy way to make certain data stand out, by putting it in a table!
  • Conditional Formatting – As a data analyst, it is very useful to add conditions (rules) to data sets in order to find what you are looking for quicker. Conditional formatting allows you to do this e.g. a basic use would be to sort the top 25 out of a list of, say 100, conditional formatting would allow you to apply this rule and highlight those that met the condition i.e. the top 25.
  • Decrease Decimal – A simple tool to remove (and round) a decimal point.
  • E-mail – Need to send your data to a client/agency? This sticks the Excel sheet straight into an e-mail for you to send on.
  • Fill Colour – Fills the selected cell[s] with a selected colour. Useful when combined with sorting according to colour!
  • Filter – One of the most useful tools as a data analyst, allows you to filter based on pretty much whatever you want.
  • Font Colour – Changes the selected cell[s] font colour.
  • Increase Decimal – A simple tool to add (and round) a decimal point.
  • Insert Chart – Plots and creates a chart based on the data that you have selected. A useful tool as a data analyst to present to clients/agencies.
  • Insert or Remove Page Break – Needing to print your data? This will display the breaks where your data will run on to other pages.
  • Insert SmartArt Graphic – A new addition (well, that actually works) to Office 2007, very useful for making data more attractive to those that will simply not pay attention to it during presenting. I often use this to create venn diagram when relating search terms to different brands in the same SEO campaign. Or (arguably it’s most useful purpose to me) creating visual and thematic sitemaps.
  • Line Colour – Changes the selected cell[s] line colour.
  • More Borders – Opens up the borders menu, an overlooked tool that helps break up data to make it more readable.
  • Paste Formatting – Pastes the formatting chosen for other cells in order to make them the same. Note: This doesn’t change the data within the cell – just the way that it looks.
  • Paste Formulas – Pastes the formulas applied in a cell to other selected cells.
  • Paste Special – Allows you to perform a variety of paste functions (including values and formulas, already mentioned) and also to combine these in clever ways. For instance, to paste ‘transposed’ and ‘as values’.
  • Paste Values – If a formula has been applied to a cell/series of cells then sometimes it is hard to move and manipulate this data without getting the dreaded ‘#value’ appearing, paste values does exactly what it says on the tin and pastes the values produced by the formula enabling you to manipulate the data further. This is probably one of my most used functions in Excel as a data analyst (hence why it is on the far left-hand side of my toolbar).
  • PivotTable – Creating PivotTable’s is an incredibly powerful way of looking at data in detail, however this could take an entire blog post in itself, so I’ll leave it at this for the sake of this guide.
  • Print Titles – Ever printed an Excel spreadsheet and never known what order the pages should go in? Print Titles repeats a defined row (usually the header row) at the top of each page.
  • Remove Duplicates – A simple but useful tool that removes all duplicated cells based on what define as a ‘duplicate’ this could be just a single primary key field/column (e.g. keyword in a list of keywords for SEO) or all columns. Becuase of this, it is a very useful tool when creating spreadsheets for Keyword Research in SEO.
  • Save As – Although save is ‘control + s’, if you use Excel in a professional context, it is always useful to save multiple versions.
  • Save As Other Format – As above, but this allows you to save your document in other formats [obviously…].
  • Sort – Another very useful tool as an analyst, allows you to quickly sort your data based on your desired condition.
  • Sort Ascending – The same as the above, however I use this more for SEO and quickly sorting the low traffic terms.
  • Sort Descending – The same as the above, however I use this more for SEO and quickly sorting those high traffic terms.
  • Top Align – Not useful for much more than making your data more readable, but still used a lot by me.
  • Wrap Text – Have you typed too much in a column and now can’t read it without adjusting the format? Just wrap the text and you won’t have to go resizing all your columns.

I prefer to have the Quick Access Toolbar below the Ribbon, to do this select the check box on the bottom left of the window before pressing OK.

Showing the Quick Access Toolbar below the ‘Ribbon’, in my opinion, makes it easier to use and therefore increases your efficiency

Also, now is when I take the time to arrange the Toolbar positioning the commands next to other relevant commands and ordering them based on how regularly I use each of them, below is the outline of how I assign my commands [I advise you take some time adjusting these according to your preferences]:

My fully customised and optimised toolbar, ordered in groups according to what I use each function for and from left-to-right based on how often I use each function

As I stated before, customising your toolbar is a very personal thing most probably based on your level of expertise and use of Excel but hopefully this guide has opened up your eyes to some of the powerful features it can be used for and made your life a bit easier…