Excel spreadsheets are a useful tool in any business and, although they can be time consuming to build, it is worthwhile to reduce the time you take to build your spreadsheets by learning as many shortcuts as possible. Find below some hints and tips to using spreadsheets:
- How to restrict users to using only a certain range of cells within one or more worksheets ?
Navigate to the worksheet you wish to restrict and then press Alt + F11. The Visual Basic Editor is now displayed. If the Properties dialog box is not displayed, then press F4 to display it. One of the Properties within the dialog box is called ScrollArea – the range that you enter into here will be range that the user is restricted to using.
Tip – Another way to get into the Visual Basic Editor is to right click on the worksheet name tab and then and select view code.
Caution – The user can still navigate to and alter a cell outside the restricted area by typing the cell reference into the Name Box located above cell A1.
Big Problem – When you re-open the workbook, even after you have saved it – the ScrollArea restriction that you have just set disappears. To solve this problem you need to enter a piece of VBA code that will set the restriction the moment the workbook is opened.
- Groups rows and columns
In Excel 2007/10, select a range of rows or columns, then click the Group dropdown menu on the Data tab and choose Rows or Columns. Now click on the Group button and a bar will appear above the spreadsheet, with numbers showing the different levels of grouping.
- How to hide a worksheet so that it does not appear in the Format > Sheet > Unhide… Dialog Box ?
Right click on the worksheet tab that you want to hide and then select view code. Then press F4 to view the Properties Dialog Box. There is a property called Visible – change this to xlSheetVeryHidden. Note that you must have at least one sheet visible at all times.
- Reveal formulae
To quickly see all formulae used in an Excel spreadsheet, hold down Control and tap the ‘key (the button to the left of the number 1 on most keyboards). Press it again to reverse the change.
- Remove duplicate data
To find and remove duplicate data items in Excel 2010, select the relevant area then click Remove Duplications on the Data tab. If there are column headings, select ‘My data has headers’ and click Select All. Click OK to remove duplicates.
- How to find out the name of the last user who opened an Excel file ?
When you open an Excel file, your name is embedded in the file. This occurs even if you do not save the file.
Note – This behaviour does not occur if the file is a read-only file, or if personal information has been removed. To remove personal information from an Excel workbook select Tools > Options > Security and then check Remove personal information from file properties on save.
The name that is stored is the user name listed in the Options dialog box – Tools > Options > General.
To see the name simply open the Excel file using any text editor such as Notepad or Microsoft Word. The users name will appear somewhere within the text.
- Show recent documents
In Excel 2010, open the File menu, click Options. Click Advanced options then scroll down to the Display section and type the desired number into the ‘Show this number of Recent Documents ‘box.
- Remove unprintable characters
Data imported from external sources can sometimes contain unprintable characters. To remove these from data contained in cell A1,say, choose another cell and use formula=CLEAN(A1).
- Clearer numbers
It’s possible to have Excel abbreviate large numbers, so that 1,000,000 become 1M and 1,000 becomes 1K, for example. To do this, select the relevant cells and press Control and 1 to show the Format Cells window. Select the Number tab, choose the Custom option then click in the Type box and type [>=1000000]0,,”M”;[>=1000]]0,”K”;0(with no spaces).
- Customise the ribbon
Click File followed by the Customize ribbon. Click New Group at the bottom right and then Rename. Type a name and click OK. Now drag and drop icons from the left-hand column into the new group.
- Customise the Status bar
The Status bar at the bottom of the Excel window can be customized. Right-click on an empty part and chose an option from the pop-up menu.
- Text to columns
If you have a list of forenames and surnames in a single column, it’s easy to separate them. In Excel 2003, select the affected column then open the Data menu and choose Text to Columns. Choose Delimited, click Next and tick the box (or boxes) representing the characters that delimit the names (typically a space). If the preview looks right, click Next followed by Finish.
- Check formulae
In Excel 2003,open the View menu, point to Toolbars and choose Formula Auditing. On the toolbar that appears, troubleshoot problems.
- How to count the number of words within a cell ?
Type the following formula =LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1 – assuming that you want to count the number of words within cell A1.
- Better Autofill
Autofill is a great timesaver but if you need to lock certain cell references before using it, then append a ‘$’ symbol before both the letter and number, so A1 would become $A$1.
- Is there a way to count the number of words within a range ?
Type the following formula =SUM(LEN(TRIM(A1:C3))-LEN(SUBSTITUTE(A1:C3,” “,””))+1)-SUM(IF(ISBLANK(A1:C3)=TRUE,1,0)) – assuming that you want to count the number of words within the range of cells A1:C3.
After you have entered this formula you must select the cell that has the formula and then press F2 and then Ctrl + Shift + Enter to make this an Array Formula.
For over 5 years ediaro.com (a member of Fladio International Nigeria Limited) has provided web solutions and advertising for public and private sectors. Our main division is Computer and Information Technology. Thanks to our flexible approach towards the development of new solutions, we have often been at the inception of digital revolution and incorporating the most recent technologies. When you partner with ediaro.com, you are associating with one of Africa’s best company for your ICT needs, we are the 1st Nigerian Company listed on the Economic Community for West African States (ECOWAS) ECOBIZ computer session in 2010. Focusing on quality, reliability and design of our products and services has brought us customers and partnership with local and Multinational Companies such as United Bank for Africa Plc (UBA), Interswitch Limited, Stanbic IBTC Bank Plc among others. Our current customer portfolio covers individuals, companies and Government agencies across the globe including Nigeria, India, United Kingdom, Mozambique and United States of America.