You can password-protect a sheet from being edited very easily, by selecting Protect Sheet from the Review menu on the ribbon. However this standard protection doesn’t stop the user from being able to view the sheet, it just prevents them from being able to edit the sheet, choosing whether to enable filtering, formatting, etc. Imagine Read More …
Month: January 2017
Go To Special
The ‘Go To’ and ‘Go To Special’ functions can help you audit your worksheet and also act as a tool to update values and make changes to your spreadsheet with the minimum fuss. From the Home tab, they can be accessed under ‘Find & Select’ from the Editing group. There are also Read More …
Show pivot table results as a percentage
If you want to display your pivot table values as a percentage of the total, there is a simple solution. You don’t need to create a new calculated field, you can just right-click the values, select Show Values As, % of Grand Total. As well as showing the values as a % of Grand Total, Read More …
Power Query (Get & Transform) overview
This overview aims to provide a basic understanding for Excel users who haven’t yet got to grips with Power Query, to get you up and running with it. If you are already accustomed to doing whizzy things in Excel with macros and VBA, or you have experience with Graphical User Interface (GUI) query tools like MS Read More …
Play audio on Microsoft Excel
A little known, quite funky Application command in VBA is to set your spreadsheet to talk to you. You can either input a fixed message like the example below, or use strings to trigger Excel to speak out loud the contents of a cell on a particular event. Application.Speech.Speak “Have a nice day”
Using the Quick Access Toolbar in Excel
If you have commands that you use over and over again in Excel, you can speed things up a bit by adding them to the Quick Access Toolbar in the top left of your screen. Rather than having to find the command in the ribbon, you are always just one click away. This Read More …
Shortcut to show and hide sheet tabs
Hiding the Sheet Tabs is something that can be very effective and useful if you are sharing your Excel workbook with colleagues. You may not want them going in and meddling with your calculation sheets, you may have added navigation buttons for the users instead or you may just want the workbook to look more Read More …
Data cleansing functions
When you import data from an external source, you will often find it isn’t in a desirable format for you to analyse. You will need to clean your data up first and thankfully there are lots of in-built Excel functions which can help. CLEAN can be used to remove non-printable characters you’ve imported. Read More …
Power BI add-ins
What is BI? BI, or ‘Business Intelligence’, is the generic term for the process of transforming data into meaningful information to increase understanding and awareness around a business and help management to make informed decisions. In recent years corporate organisations have really started to see the value in developing their Business Intelligence. Whether the Read More …