Freeze titles for added visibility in Excel

Freezing Titles (or panes)

Whenever you’re working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, including the ability to freeze panes and split your worksheet.

You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.

To Freeze Rows

  1. Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we’ll select row 3.

  1. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.

  1. The rows will be frozen in place, as indicated by the grey line. You can scroll down the worksheet while continuing to view the frozen rows at the top. In our example, we’ve scrolled down to row 18.

To freeze columns

  1. Select the column to the right of the column(s) you want to freeze. In our example, we want to freeze column A, so we’ll select column B.

  1. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.

  1. The column will be frozen in place, as indicated by the grey line. You can scroll across the worksheet while continuing to view the frozen column on the left. In our example, we’ve scrolled across to column E.

Freezing Top Row or First Column

In my (humble?) opinion, this is quite poorly named (or at least not as clear as it could be!).

Most of the time, when working with Excel data, everything is based on the data block that you’re working with. So, for example, tell Excel to do a sort & it assumes that the first row are your headers, create a table & the same thing…

So you would expect that Freeze Top Row or Freeze First Column from the drop-down menu would relate to the first row or column of your data… but no, it’s not that straightforward… it refers to the first Visible row or column that you can see. Most tutorials refer to Column A or Row 1 & if you’re at the top of your spreadsheet, then it’s likely that these are the rows that will be frozen… however, if the first row that you can see is Row 7 & the first column Column C, then these are the ones that will be frozen

To unfreeze panes:

If you want to select a different view option, you may first need to reset the spreadsheet by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.

The Curse of the cAPS lOCK key

The curse of the cAPS lOCK key and how to solve it

OK, so admit it… we’ve all done it… started typing with the CAPS LOCK key on & only noticing that after a paragraph or two we’ve typed…

vIDEO PROVIDES A POWERFUL WAY TO HELP YOU PROVE YOUR POINT. wHEN YOU CLICK oNLINE vIDEO, YOU CAN PASTE IN THE EMBED CODE FOR THE VIDEO YOU WANT TO ADD. yOU CAN ALSO TYPE A KEYWORD TO SEARCH ONLINE FOR THE VIDEO THAT BEST FITS YOUR DOCUMENT.

tO MAKE YOUR DOCUMENT LOOK PROFESSIONALLY PRODUCED, wORD PROVIDES HEADER, FOOTER, COVER PAGE, AND TEXT BOX DESIGNS THAT COMPLEMENT EACH OTHER. fOR EXAMPLE, YOU CAN ADD A MATCHING COVER PAGE, HEADER, AND SIDEBAR. cLICK iNSERT AND THEN CHOOSE THE ELEMENTS YOU WANT FROM THE DIFFERENT GALLERIES.

Makes you feel like this, doesn’t it?

Resulting in…

And then, of course, we have to delete it all & start typing again…

… or do we?

Avoiding the retype

No, we don’t have to retype the whole thing, here’s a few simple ways to resolve it painlessly.

ŒFirst select your text
From the Home tab
ŽSelect the Change Case button
Select the appropriate option (in this case it would be tOGGLE cASE). As you can see, the various options available themselves illustrate what their effect will be

Alternatively, you can select the text & use Shift+F3 to cycle through the various options

Removing Leading & Trailing spaces in Word

How to remove leading and trailing spaces in Microsoft Word

Leading & trailing spaces

Sometimes (either because we’ve copied the text from elsewhere or we’ve used the spacebar when we didn’t mean to) we can generate a lot of extra spaces both at the start and end of paragraphs.

This can make your document look a little untidy – especially when wanting to format it neatly afterwards.

As these spaces are difficult to see, switching on Show/Hide (Home Tab, Paragraph Group) shows these a little more clearly by replacing the space with a “.”


Spaces are now more visible – as are the end of paragraph marks

So, how can we get rid of those spaces?

Word, itself, does not provide a simple & straightforward method to remove such unwanted spaces (unlike the button to quickly change the case of text). This means that most people remove them manually by pressing the backspace or delete keys until they’ve all gone; to say the least, this can be really time consuming – especially in a large document.

So, again, how can we get rid of those spaces?

  1. First of all select all of the lines with leading and/or trailing spaces
  2. Press Ctrl+E to centre align them
  3. Keeping the lines selected, press Ctrl+R to do right align. This will remove trailing spaces
  4. Still keeping the lines selected, press Ctrl+L to do left align. This will remove leading spaces

And that’s it! Now your Word document will be devoid of those extra leading and trailing spaces. Only neat and clean sentences will remain.

Selecting the relevant sections

If your whole document has additional & unwanted spaces, then the quickest way to select the whole document is to press Ctrl+A. Alternatively, You can select a single row by pointing to it with your mouse from the right hand margin of your document

Click here once and a single line is selected

Click twice and the whole paragraph is selected

Clicking three times selects the whole document (but Ctrl+A is quicker)

And just as a reminder the Ctrl key is (usually!) the bottom left hand key on your keyboard & usually looks like this

How to remind yourself of the last item on a PowerPoint slide

The final bullet on a PowerPoint slide

This isn’t one of mine, but a tip that I picked up whilst on a training session run by the Business to Business networking group that I belong to (www.bforb.co.uk) & it came from Paul Heath, from Ethical Business Mentoring Ltd (www.ethicalbusinessmentoring.co.uk) who was running the session.

Use your laptop as an autocue

When using PowerPoint to aid a presentation, in an ideal world, we will be able to set up such that we can use the laptop that we’re using to run the presentation between us (the presenter) and the audience & use it as an autocue which will enable us to see what is up next on our slide deck and when we are about to make our final point on that slide.

However, on many occasions & for various reasons, we might not be able to achieve this; so how can we easily & confidently set up PowerPoint so that we will always know when we’ve reached the last point on a slide & not fall into the trap of thinking that we’re about to move on to a new slide only to be “thrown” by that final bullet point that we’d forgotten about?

Practice!

Obviously, a key answer is to avoid using bullet points & practice, practice, practice… but sometimes bulleted lists (whether with the actual bullets, or not!) are the most effective way of covering a topic and during the “adrenaline rush” (or nerves) that can accompany the delivery of a presentation, it can be easy to lose track.

So, what can we do?

The final bullet

Simply use a full stop after the FINAL bullet on the slide, only. Don’t end any of the other items on the slide with any punctuation & that way, the full stop will stand out & act as an aide-memoire that the next “click” will take you onto the next slide.

As with all such tips; simple, but effective!

BODMAS and why it’s important to understand it in Excel

BODMAS

BODMAS is a way of remembering the order of operation that Excel follows when it evaluates values in a formula.

BODMAS stands for:

B – Brackets
O – Order (Indices such as 22 etc.)
D – Division (in an Excel formula we use a /)
M – Multiplication (in an Excel formula we use a *)
A – Addition
S – Subtraction

Example:

What is 4+6/2*3-1?
If you “read” the formula (left to right) you will get 14
If Excel calculates it for you, the answer is 12

These are two different answers, but only one is correct.

In BODMAS, division & multiplication come before addition & subtraction, so what Excel has done is the following…

  1. Divide (there are no brackets or “orders”): 4+6/2*3-1 ⇒ 4+3*3-1
  2. Multiply: 4+3*3-1 ⇒ 4+9-1
  3. Add: 4+9-1 ⇒ 13-1
  4. Subtract: 13-1 ⇒ 12

Of course, if we needed the answer to be 14, then we could have achieved that by using brackets – Excel is still applying BODMAS, but we’re forcing it to calculate in the order that we need, so…

(((4+6)/2)*3)-1 ⇒ 14 – Excel starts with the “innermost” set of brackets & works outwards, so in this case Red, then Blue, then Green

So why is this important?

Why all of this is important is that Excel will calculate this way unless you force it (by using brackets) to do something different… with numbers it might be obvious what has happened, but if you’re building a spreadsheet creating formulas (especially where you do not, yet, have any data) you might enter a formula that “makes sense” to you, but which Excel may treat differently… which is why I always advise people to build spreadsheets with dummy numbers (3, 4, 5, etc or 10, 20, 30) which will test their calculations and, hopefully, easy to check the answers without a calculator!

When testing, I don’t use 0 (zero) in case addition or subtraction is involved (the numbers won’t change), I don’t use 1 for the same reason but with regard to multiplication & division & as 2+2 = 2×2 (2’s can be dangerous!!)

Case study: DSL Engineering

Summary

Using Microsoft Excel spreadsheets to generate effective management dashboards

The Client’s problem

DSL Engineering - Excel dashboardThe client, an engineering company, produced lots of statistical data about their business: the amount of work invoiced, the number of jobs completed, work in progress, new quotations, etc, but it was all held in separate spreadsheets making analysis time consuming & difficult. This also meant that whilst they had lots of data, they had little information.

How we solved it

We created a single spreadsheet to pull all of the information collated together. From this we then produced a series of charts to show the information in a much more user-friendly way. We then created a series of custom views (driven by macros) to allow them quickly switch from data input to a review of the current month’s information. These charts were then grouped together to form a single dashboard for the company.

The benefits for the client

The data that was collected is now presented quickly and in an informative way via the dashboard allowing a clear picture of activity & progress to be ascertained. In turn, this allows key decisions to be quickly taken based upon accurate & up to date clear information.

Case Study: Ashford & Co – Chartered Accountants

Summary

Using Excel and Word to allow an effective MailMerge saving time, effort and money

The Client’s problem

Ashford and Co - MailmergeOn a regular basis, this accountant sends out a printed newsletter to each of his clients; following each budget, he also sends out a “Tax Facts” card. Periodically during the year, he also sends out specific information to sub-groups of his client base pointing out relevant information.

In order to do this, he would have to go through each of his client’s files to check their contact details & any specific information that they needed to be informed of. This was very time consuming

How we solved it

We created a simple Excel database that not only listed his clients & contact details, but also grouped which information they needed to be aware of, for example “Pensions”, “Rental Income”, “Company Annual Return due”. We then created some standard template letters which we linked to the database via Word’s MailMerge feature allowing him to quickly generate any necessary letters filtering out any as required.

We also showed him how to perform the MailMerge “from scratch” to allow him to create his own new letters where necessary

The benefits for the client

Many hours of manual work was now saved by a much more streamlined process. In addition, he could be certain that the correct letters were sent to the correct clients with a much reduced chance of human error creeping in.

In addition, the Excel spreadsheet can now be used for additional marketing processes and client management.

Case Study: Adrian Cunningham LLB ACA – Chartered Accountant

Summary

Creating a Powerpoint presentation to help with a client rebranding

The Client’s problem

Adrian Cunningham LLB ACA - PowerPoint presentationAccountants have always “suffered” with a perception of being “boring” & “grey” – all courtesy of the Monty Python sketch: “The Vocational Guidance Counsellor”. So, when we were approached by a colleague from that profession, we were pleased to note that the recent rebranding that he had commissioned was bright & vibrant. However, the PowerPoint presentation that he had produced to support his Business Spotlights at networking events was more Python than Panache & certainly didn’t reflect his new branding; we were challenged to help.

How we solved it

The first stage was to obtain from his graphic designer copies of his new images, including details of the colours & fonts used. These were then incorporated into a vibrant presentation, reworking the key points from his original draft to ensure that all of the key points were included and ensuring that the colours, fonts & logos matched his new rebranding to help reinforce this.

The benefits for the client

The client received a presentation that focused on the 5 key areas of his business with sufficient information to engage & inform his audience in expanding on these themes, whilst at the same time being able to be delivered, without rushing, in an 8-10 minute timeframe.

In addition, we produced a “timed” version looped to run continuously to support attendance at seminars and exhibitions.

Case Study: Steel Stockholder

Summary

Using Excel spreadsheets more efficiently to reduce manual input and speed up the generation of information

The Client’s problem

Client had a number of spreadsheets with totals needing to be copied from one to another to allow input of rebates from suppliers & calculation of final Gross margin. During the existing processes, sales significantly above or below expected Gross Margins were manually checked for errors & then adjusted to account for rebates which may have brought the margin into “acceptable” levels.

The analysis work didn’t start until the end of the month when sales reports were available as it was a time consuming process to get the information from the system reports into their Excel spreadsheets.

This meant that much manual effort was required to arrive at an accurate gross margin figure and any correcting invoices or credit notes were not processed until the following month

How we solved it

First of all, we ascertained that it was possible to run the system reports at any time during the month and that it could be saved to a text file (csv would have been better, but that wasn’t possible). We then developed a macro to split the text file across a number of columns and strip out the unnecessary (repeated header) information from the text file when opened in Excel.

We then built a number of lookup tables in a “master spreadsheet” for the amount of rebate available by product type from each supplier and also for customer/rep/area information together with the necessary formulas to allow the data from the latest report to be recalculated to generate both revised (and accurate) gross margins. This was then further analysed using filters and Pivot Tables to allow margins outside of agreed parameters to be quickly checked & sales & margin performance by customer, rep & area quickly identified

The benefits for the client

As the detailed information could be generated from the system at any time & much of the processing was automatic through macros, pre-populated formulas, filters & Pivot Tables, it was possible to perform the analysis on a weekly, rather than monthly basis.

This had a positive impact on cash flow as any necessary supplementary invoices or credit notes could be issued during the relevant month, rather than, as previously, in the following one; customers then had no excuse not to pay on time.

In addition, sales & margin analysis was much more timely allowing for management decisions to be made more quickly with more accurate information.

Testimonial from GEM Business Support

Excel database for Mailchimp

I had a client who urgently needed an excel database putting into MailChimp for a campaign. There was 4,000 people on the list. The names had been logged in a single column which meant that I wouldn’t be able to personalise the campaign. I emailed Colin and asked if it was even possible to split a single excel cell with a first and last name into two columns. Within minutes Colin had sorted this for me. I can’t thank Colin enough, and neither can my client. What looked like a long job was done in seconds.  It truly pays to know Colin, his knowledge around excel and word is invaluable.  Thank you

Gill Arnott: Gem Business Support