Daily Archives: May 17, 2013

How to Customise the Microsoft Excel Ribbon Menu

Customising Microsoft Excel 2010
When Microsoft Excel 2007 was 1st released, many new users struggled to come to terms using the new Ribbon style of navigation. One aspect which baffled people the mist was basically the way to open and save documents. There appeared to be no technique to carry out these uncomplicated acts as there was no longer a ‘File’ menu choice. Unbeknown to quite a few, these selections have been now to all intents and purposes hidden way behind the so-called ‘Office Button’. The concern was that lots of people today thought that the Office Button as just the Microsoft logo and so didn’t even feel to click it! This concern has been addressed by Microsoft in the most up-to-date 2010 version of Excel, by replacing the button with the familiar ‘File’ menu. Appears like Microsoft listened this time! In this report we shall look in the different possibilities for customising Excel 2010 as a way to get the most effective out from the computer software.

The Quick Access Toolbar
The Quick Access Toolbar will be the little menu which can be situated by default above the key Ribbon menu. When the computer software is first loaded it only capabilities three buttons: Save, Undo and Redo. Nonetheless, the range of tools might be pretty conveniently be extended by clicking the smaller black drop-down arrow which is situated in the ideal with the menu. Selecting this reveals a short list of added tools which may be added to the toolbar by clicking alongside, which has the impact of ‘ticking’ the selection and adding it towards the toolbar. Additional tools can be added by clicking ‘More Commands’ in the bottom of the menu and choosing from a big selection of tool options. Lastly, the position of the Quick Access Toolbar itself may be changed by clicking the menu and selecting ‘Show Beneath the Ribbon’. My personal view is the fact that this isn’t a especially fantastic position for it as it takes up further space. In its default position It shares the identical space because the document name across the top rated and represents a far more logical place for it to become.

The Recent Documents & Folders List
The recent documents list was around in the 2007 version of Excel, but the current folders facility is new to Excel 2010. They both feature little grey ‘pins’ which when clicked have the effect of ‘pinning’ your documents and folders onto the brief list of recent files. This is a quite useful function and stops your most used documents from dropping off the bottom with the list. It doesn’t actually pin them to the exact same spot and you will find that they will move around the list, but at least they won’t be lost for good as you open far more documents.

The Ribbon
Last but not least, the Ribbon itself could be customised by adding tool buttons which you use frequently. To do this, 1st click the File menu and select Options. Now select ‘Customise Ribbon’ and then click ‘New Group’. It is necessary to create a new group so that you can additional buttons towards the Ribbon toolbar. Tool buttons can now be added to this custom group by choosing from the menu on the left and then clicking ‘Add’. Finally the custom group might be given a user-friendly name by clicking the ‘Rename’ button.

Once we’ve become familiar using the new Ribbon style of navigation in Excel 2010, most customers do seem to prefer the system. Coupled using the facility to simply customise the Ribbon to suit your personal requirements, Excel 2010 would appear to become the best version yet offered by Microsoft.

How to Password Protect Your Excel Spreadsheets

It can be very useful to password protect spreadsheets to make certain that other people aren’t able to accidentally (or even deliberately) over-type important data or formulas and functions. Having said that, there are occasions when when you will need to protect some cells but permit other users to gain access to and edit other cells on the same spreadsheet. It could seem that this is something that may could be hard to achieve, but in fact it is not difficult to do

First Set up The Spread sheet
First of all we must understand what happens when we password protect a spread sheet in MS Excel. Let us begin by getting an understanding of what takes place when we password protect a spread sheet. Automatically, all cells in Excel are locked when protection is applied to the spreadsheet. The default setting in Ms excel is for all cells to be locked when we encrypt the spread sheet. What we must do first is tag any cells which we don’t intend to protect. We do this by clicking in the cell, or cell range which is not to be encrypted, then clicking the right mouse key and picking ‘Format’. Now we choose the tab called ‘Protection’ which produces a dialogue box containing two tick-boxes named ‘hidden’ and ‘locked’. The default is for the ‘locked’ option to be ticked automatically, with the ‘hidden’ option being un-ticked. The effect of this is that if we apply a password to the spreadsheet, every one of the cells are going to be locked at the same time. What we need to do is un-tick the ‘locked’ box and then click the ‘OK’ button. Your spreadsheet is now ready to be password protected.

How to Password Protect the Spreadsheet
The next stage is to select the ‘Review’ tab on the Ribbon and click the ‘Protect Sheet’ tool button. What we see now is a popup with an empty password field. It also has a number of tick-boxes which allow you to choose what can and can not be done after the spreadsheet is encrypted. The default position allows for the selection of locked and unlocked cells, but nothing else can be carried out when the spread sheet is locked. Next we type in any chosen password into the box and press the ‘OK’ button. Now we’re expected to re-enter the same password to double-check that it has been typed in correctly. Your spread sheet will now be password-protected, but you can still modify the cells that you choose to be unlocked To remove password protection, click on ‘Unprotect Sheet’ then enter the password.

How can you Unprotect the Spreadsheet if You’ve Forgotten The Password
You could think initially that if you lose password then there is no way to get back in to your worksheet. However, there’s a simple way round the protection, depending on what actions you have allowed before inputting your password. We found that by default, Ms excel allowed for the selection of both unlocked and locked cells when we protected the worksheet. So long as this has been allowed, the answer is in fact quite simple. The solution is to basically copy the entire spread sheet, and then paste it into a new workbook. The data as well as formulas will be copied into the workbook, however it will not be password encrypted. The first spreadsheet can be deleted and you can name this new workbook the same as original. Effectively, we have now got the original spreadsheet yet it’s no longer password-protected. If on the other hand we had previously un-ticked the first two allowed actions, we wouldn’t be able to select any cells and hence would not be able to copy and paste. To stop any one from copying and pasting we simply have to un-tick the first two options. Be very careful though that you do not lose your own password

Three Ideas which will make Producing PivotTables Much easier

PivotTables are really essentially the most usable of all tools in Excel; however their use triggers some worry with a great many users. This is really thanks in part because there seems to be no absolute procedures when setting up PivotTables. Should you have let’s say 8 columns of information this will make eight PivotTable ‘fields’, yet there are just 4 ‘field areas’ – so precisely where can they all go?! The answer is that though there are no distinct rules for the assembly of PivotTables, there is certainly three very useful tips that can help a lot whenever building them.

The first principle is to at first identify all of your actual ‘value fields’. These types of fields will essentially always require being put into the value field area that’s situated in the bottom right hand area of the values pane. It doesn’t make any difference how many values there are as you’re able to simply add them on top of each other just by clicking on and dragging in to the precise location. The actual order that you stack these values will determine the actual sequence in which they appear from right to left in the PivotTable. Your 1st value field will show up in column A with the next one down within the list showing up in column B and so forth. In this particular way you can easlily handle a good deal of your columns of information in one go.

The second guide concerns the row fields. Similarly to value fields, the order in that you load these will determine the particular sequence by which they appear inside of the PivotTable itself. One particular great piece of advice is always to think about fields which are to be positioned in rows and gauge just how many separate areas of data relate to each one. For example you might have one field for months and another for quarters, there’ll of course just be four quarters compared to 12 months. If you therefore just click and drag the quarters to the row section 1st, followed by the months, you’re going to set up a type of data hierarchy that makes filtering along with analysis considerably easier.

The last tip is in respect of the ‘Report filter’. Bringing columns directly into this field establishes a filter which sits outside of the actual PivotTable itself. This enables us to filter all the date in the PivotTable in just one action. Just what exactly will be valuable regarding the Report filter is the fact that since it is established outside of the main table, we’re able to bring many fields into this location that we might otherwise find it difficult to find a practical place for. As a result, all the fields which you have remaining after planning the basic PivotTable may easily be moved straight into the report field section, supplying you with better filtering capability.

PivotTables are an hugely useful method in Excel, however, many continue to be put of utilizing them due to the amount of data in their spread sheet. The hope is that this article could actually inspire more people to play around with them ultimately include them into their routine office work .

A Simple Invoice System Using Microsoft Excel & Word

Your invoicing can be made easier and more productive by using a basic method integrating both Excel and Ms word.

First you need to create an Excel spreadsheet and format it in an appropriate manner, keeping in mind that this will form the basic structure of your invoice and will eventually be seen by your clients. It isn’t necessary to have any information relating to the Company (such as address, telephone etc.) as we will include these in our Word template in the next stage.

Now we need to create a Word document which will act as a template for all or our invoices. Leave the main body of the document empty as this is where the Excel spreadsheet will be embedded.

Now switch to your Excel spreadsheet and select the entire data table which you have created as the basis of your invoice. We now need to copy the data, then open your Word template and select where you want the data to be placed.

The next stage is dependent upon your version of Microsoft Word. If you are using Microsoft Word 2007, you will need to click the Paste button on the Home tab of the Ribbon.

Now you need to select ‘paste special’ and in the dialogue box choose ‘paste link’ and select Microsoft Excel Worksheet Object. Microsoft Word 2003 users will need to select ‘Edit on the main menu bar, followed by ‘Paste Special’. The dialogue box that appears is the same as that for Word 2007.

We now have a live dynamic link between our Word template and the Excel spreadsheet. The result is that if we modify any of our data in the Excel spreadsheet the Word invoice will update itself. Simply right-click the embedded object in Word after editing the spreadsheet and choose ‘update link’ to see the changes. Also, each time that the Word invoice is opened you will be prompted to update.

And that’s all there is to it, really quite an easy technique! It is however useful in several ways. Invoice data can be automatically calculated using formulas in Excel whilst presenting it in a professional manner to your clients. And by removing the necessity to duplicate data you cut down on the possibility of introducing errors.

I hope this simple technique may prove useful to you and your business.