Archive for the 'Tips and Tricks' Category

Excel Tips and Tricks: How to Use the Excel Freeze Function

How frustrating is it when you scroll across a spreadsheet, and all the useful information disappears? Give your memory a break with the excel freeze function!

Excel Tips and Tricks: Freeze Function

Excel is an incredible tool for storing and managing data. However, scrolling across or down a spreadsheet is inevitable when dealing with magnitudes of cells. It would be ever so helpful to see the column and/or row titles while navigating other areas of the spreadsheet to make sense of and compare data.

BAM! In walks the Excel freeze function to save us all from repetitive strain injury.

Excel Freeze Function

So where can you find this handy tool?

From the open spreadsheet, select the ‘View‘ tab and then the ‘Freeze Panes‘ function, which is found in the ‘Window’ group of the View tab (Select View > Freeze Panes).

From here, you can see three options:

  1. Freeze Panes
  2. Freeze Top Row
  3. Freeze First Column
Step 1. Select View
Step 2. Select Freeze Panes
Freeze Pane Options

To get to grips with how to use the different Freeze options, check out the examples below!

1. Freeze Top Row

From freeze panes, select ‘freeze top row’.

Excel will automatically freeze the first row of the spreadsheet, which is indicated by a dark grey horizontal line beneath the first row.

2. Freeze First Column

From freeze panes, select ‘freeze first column’.

Again, Excel automatically freezes the first column of the spreadsheet, which is indicated by a dark grey vertical line to the right of the first column.

3. Freeze Panes

The above are extremely straightforward but don’t allow much flexibility. Freeze panes is slightly more complicated but gives more freedom.

For example, you can freeze multiple rows, multiple columns or multiple rows and columns. 

3.1 Freezing multiple rows

If you were looking to freeze a certain number of rows, select the row below the last row you want to remain visible and select freeze panes.

For example, if you want the first three rows to remain visible, select row four and freeze panes.

A dark grey horizontal line above row four and below row three indicates that these first three rows will remain visible whether you scroll up or down.

3.2 Freezing multiple columns

Freezing multiple columns is similar to the above example of freezing multiple rows.

Select the column to the right of the columns you want to remain visible and select freeze panes.

For example, if you want the first three columns to remain visible, select column four and freeze panes.

A dark grey vertical line to the left of column four and the right of column three indicates that these first three columns will remain visible whether you scroll left or right.

3.3 Freezing rows and columns

But what do we do when we want to freeze columns and rows?

Instead of selecting an entire row or column, we set one cell that controls which columns to the left and rows above are visible.

For example, if we want to freeze the first row and column, select the cell two rows down and two columns across (B2).

A dark grey vertical line to the left of the cell and a dark grey horizontal line above the cell indicates that the first row and column will remain visible whether we scroll across or down the spreadsheet.

4. Unfreeze Panes

After applying one of the three Freeze Pane options, the top Freeze Pane option will be replaced with ‘Unfreeze Panes‘. Simply select this to remove all columns and rows that have been frozen.

Unfreeze Panes

Thanks for reading, and we hope this helped! For more tips and tricks, check out our other blogs here!

Want more hands-on training? Infero also offers multiple Excel courses that will take you from beginner to pro. Get in touch for more information, book a course or receive a free consultation using the details below!

Suite 2556 37 Westminister Buildings Theatre Sqaure Nottingham
NG8 2EN

Why You SHOULD Learn New Skills In Excel

Now might be the time to get the new job you’ve always wanted. The Office of National Statistics reported in January that “The number of job vacancies in October to December 2021 rose to a new record of 1,247,000”. In some areas, however, things are still very competitive and it takes that little bit extra to stand out. 

One skill employers always look for proficiency in is MS Excel, because the software is extremely versatile and able to be turned to almost any task. It is also very widely used, in all types of industries including HR, Marketing, Retail and more. This is due to the depth of functionality that Excel has, being able to do everything from calculating, to sorting, organising and evaluating data, to providing business insights through easy to understand charts and visualisations. It is the default tool in almost every organisation, used by everyone from entry-level employees to senior staff.  

Whether you’re an employee trying to stand out from the crowd or an employer trying to raise productivity in your workplace, here are our top 5 reasons Excel should be your next step in education: 

Improving efficiency in the workplace 

Since Excel allows you to manipulate large amounts of data and even automate any data processing that takes place, it is easy to save hours of repetitive work. Tasks that take hours or days can be accomplished in minutes if you know how to access the correct tool in Excel. The greater your knowledge the more work that can be done speedily and efficiently, simply by accessing the sophisticated tools that Excel puts at your disposal. 

Knowledge of Excel will also help you stand out to prospective employers, or, perhaps, increase the appreciation that your present employer has for you, and give you a helping hand towards that promotion you are after. 

Takes the strain off the IT support 

In some organisations, where the workforce does not have a good knowledge of Excel, IT support may be required to help with ordinary, day to day work-tasks. This means that the individual may be unproductive whilst waiting for help from IT and the support staff’s time is taken up in solving an issue that wouldn’t even arise if the staff member had had some elementary training. Two staff members time are wasted on something trivial and very easily solvable. For both employees and employers, just a small amount of the right training is invaluable. 

Can Save the Company Money 

It might be that the company will at some point require someone with strong Excel skills. We live in an increasing data driven society, after all. At this point it would be far more cost effective for the employer to invest in their current staff. These are the people who have knowledge of, and commitment to, the company and could use that knowledge to uncover some money-making insights, if they only had the skills. Hiring someone already proficient in Excel will be time-consuming and costly. And it will take that person literally years to gain the knowledge and understanding of the business that current employees already have. Training is an investment of a few days that provides an excellent return in terms of workflow and productivity.  

Creates Higher Satisfaction for Employees 

Performing tasks in Excel without the proper training can mean that employees have a much higher workload than they really should, because they are not able to access the functionality contained in the programme. This, in turn, can lead to a team feeling unmotivated, with lower job satisfaction, even pressured and stressed because they may feel that they aren’t fully equipped for the task. Providing advanced Excel training for your team will increase both their motivation and productivity and your turnover. And, because they are being trained and valued, employee retention will be high.

Making Yourself Necessary 

The more efficient you become as a worker and the more time you save on the normal, everyday tasks, the more you will graduate to higher levels. Continuing your training to Excel advanced level can make you almost irreplaceable. Developing new skills helps you stay on top of your game and is a win-win for both the employee and the employer. 

If you’re looking for training for yourself or your employees, we offer Excel courses at three levels, catered for your pre-existing knowledge and experience.  

How To Identify Version Of Microsoft Office

When buying a course or looking for tips, it’s easier for the provider if you know which version of Microsoft Office you’re using. This guide will show you how to identify this on either Windows or Mac.

 

When using Windows

 

  1. Open any of your Office applications (Word or Excel) and create a new document.
  2. Choose File in the top left corner, and then select either Account or Help from the list on the left.
  3. Under Product Information, you’ll see your Office product name and, in some cases, the full version number.
  4. If the full version number doesn’t appear here, choose About Excel. A dialog box opens, showing the full version number and bit version (32- or 64-bit) at the top.

excel product

 

1 – Product name, such as Microsoft Office Professional Plus 2013 or Office Home and Student 2016

2 – Full version number, which starts with 15 for Office 2013 or 16 for Office 2016.

 

 

 

On Mac

 

  1. Open any of your Office applications (E.g. Word or Excel) and create a new document.
  2. Go to the Excel menu and choose About Excel.
  3. In the dialog box that opens, the version number appears in the middle (Office for Mac 2016) or in the top left (Office for Mac 2011).

 

excel

 

 

If you have any questions or need any help at all message us on Facebook and Twitter

 

Microsoft PowerPoint: Presentations Do’s and Don’ts – Part 2

Starting from scratch

Most people will blame the presentation, not the presenter, which is the first thing you can avoid: Pre-made slide templates. There are plenty of these templates that come with PowerPoint, with even more online, some for free and some for a price. The best thing you can do is to start with a blank presentation. Create your own presentation from scratch, personalise it and show off your style. A presentation that is adapted to the presenter is better than a presenter that adapts to a presentation.

powerpoint doc

Skip to the point

When listing a few items or objectives, many users might fall for the bullet point trap! During a presentation bullet points can take focus away from the presenter. However when you list each point one at a time with spaces and a row each it makes the point seem more clear and connected. This is improved more when you place the list next to an image or a graph that is meant to be the real focus.

list powerpoint

Thanks for reading and be sure to check us out on Twitter an Facebook!

Visual Basics Tip: Future Proofing Code

There are several ways you can future proof your code in Visual Basic. By using the Macro Dialogue Box in Excel (as well as Word) you can make sure your colleagues can understand the purpose and general use of the Macro you record or write.  To use the Macro Dialogue Box to is advantage. Here the best ways on how to do so:

 

Give your macro a user friendly name.

Always give your macros a user-friendly name, or agree a naming procedure for your Macros so that everyone knows what they are for. This may seem a bit like stating the obvious, but if your Macro is there to format a table, then give it a name that would summarize what it formats and keep it memorable and concise. Naming a Macro No.14 isn’t really going yourself or others any favors when debugging any problems.

 

Add the shift button to any key short cuts.

Always add the Shift button to any key shortcuts. A CTRL+Key shortcut will disable its MS predefined purpose, so If you add CTRL+C shortcut to your macro and run it, you will not be able to use that short key to copy text or cells.

 

Use the description box to explain the Macro.

Always use the description box to add as much info about the Macro as possible and dates when it was edited. This way you can always know what went wrong and when and by who, not to point blame but to understand what happened and minimize time spent on investigating the problem.

 

future

 

Use comments when you write a code.

Commenting can also be very handy when writing new code, recycling code or adding more details to your existing code to help others understand the purpose of your code.

 

Adding a comment is fairly easy. Go to your line of code and add an apostrophe (‘) in front of it. Anything on that line will turn green indicating that VBA is not recognizing it as a line of code, but seeing it a comment or note.

 

marco

 

 

Always use commenting creatively to lines of code you are unsure of, as well as adding notes to your code for people to see what that block of code is meant to do. You could also use the apostrophe to debug the code if it is not working correctly or clashing with another line of coding. Or instead of deleting code, you can use commenting to leave the code where it is and come back to it at a later stage.             

 

fg

 

If you want any other tips on Microsoft VBA then have a look at our other tips on Recycling Code and The Macro feature.

 

Want more information on Visual Basic? Then look no further, check out our VBA Courses and VBA Hints and Tips. 

 

Need more help? Then we’re here –> Message us on Facebook and Twitter 

 

 

 

Visual Basic Tip: The Macro Feature

Recording Macros is brilliant! Especially if you are not sure on how to word something initially. If you can’t decide what to use in your code, try recording macros that similar to what you are trying to do and see what is entailed in the code. The obvious next step would be to remove any unwanted or redundant code.

 

The first step is record a Macro. The best way to do this is click the Record Macro Button which is found on the Status Bar (the green bar at the bottom of your excel sheet).

 

njk

 

 

Then, the Record Macro Dialogue box appears.

 

 

huh

 

Be sure to fill out the Name and Description boxes with as much details as you can to future proof your Macro, as per below.

 

 

future

 

 

Click OK.  Now you will have to format the table as you would normally in Excel. Excel will record your steps as a Macro. Click the Record Macro button again to stop recording.

 

Next would be to view the Macros. To do this, Find the View Tab on the Ribbon, go to the Macro command group, and select View Macros.

 

ot

 

 

Click on the Edit button and this will bring up your Macro in Visual Basic. If you look at the screen grab below, you will see a simple Macro for selecting a range of cells and formatting it with bold, italic and adding a background colour.

 
fit

 

 

This should now give you an idea of the sentencing required to create a macro that grabs a group cells, formats it and adds background colour. Now all you have to do is use your VB know how and adapt the code to something more practical and universal that applies to all cells. Just remember to remove any redundant lines (for example .tintandShade = 0 isn’t needed so you can delete it).

 

Once your happy with your Macro, then add the code you think will work best (like looping or IF statements). By using this simple way of recording Macros and then editing them to your needs you can save yourself a huge amount of time writing code from scratch.

 

If found this hint and tip useful, you can always check out our Recycle Code tip for more ideas.

 

Want more information on Visual Basic? Then look no further, check out our VBA Courses and VBA Hints and Tips. 

 

For more hints and tips on other Microsoft Office applications sign up to our Mailing List.

 

 

 

 

 

Microsoft Outlook Tip: Setting Up a Meeting

Outlook is a great tool for not just sending and receiving emails, but for also incorporating your day-to-day happenings in the office. Calendar tools can help with setting meetings, appointments and plan your day.

 

To set up a meeting in Outlook, go to the Home tab on the Ribbon, select New Items and then select Meeting.

 

 

 

 

 

Once you do that, you will get the New Meeting Dialogue Box.

 

 

outlook.2

 

It is easy to invite people to a meeting by using the ‘To‘ box and adding a subject in the ‘Subject Box’. You can also schedule a time and date by adding them into the ‘Start/End Time’ box as shown in the picture above. It is also possible to schedule a meeting for the entire day, simple click the ‘All Day Event‘ button.

 

 

Fancy learning  more about how Outlook can benefit your business? Then have a look at our Outlook courses.

 

Let us know what you think and message us on Facebook and Twitter

Microsoft Word Tip: Creating a Header

If you want to create a poster or letter document in Microsoft word, then it could be useful to use the Header option, to give your document a professional look. The header feature allows the user to add smart looking areas in the top, bottom and side margins of a document.

 

Why is using a header or footer useful? well many online forums would suggest that when sending a document with a header, employers see a strong level of professionalism and that it shows that you have a good knowledge of using Microsoft word.

 

To use this feature, simply click the Insert Tab, and then in the Header and Footer group, Select either a header of footer.

 

Picture1

 

From the Drop Down Menu below the Header option, Select which Design you would like.

 

Design Drop Down Menu

 

Once you have selected the Design Template then you can Customise it to how you like. To customise the header,  select the Edit Header option shown at the bottom of the picture above. The customise steps are simple and easy to follow and it will give you the freedom to design exactly how you would like the header to look.

 

See our other Microsoft Word Hints and Tips.

 

Don’t forget to follow us on Twitter and Facebook.

Microsoft OneNote Tip: Recording Feature

Have you ever been sat in a lecture trying to write notes fast enough and listen to the speaker? Well maybe you didn’t know about this brilliant Recording Feature on Microsoft OneNote.

 

OneNote offers an option to create Visual and Audio notes through a recording feature that is situated upon the Top Button Tab like shown below.

 

Microsoft OneNote: Feature Tab

 

This feature helps you to  make quick notes on the go, just by simply recording in vocal mode. If you have a little more time then it could be useful to record in video mode, both visual and audio notes can be shared with your colleagues and contacts through OneDrive.

 

All you have to do is click the recording icon and it will automatically start recording, however if you record in visual mode then you may have to accept permission to access the webcam. When you would like to stop recording,  there is a large Stop button next to the icon.

 

So, if your ever in a lecture and don’t want to loose any important facts or information then don’t forget to try out this recording feature.

 

We hope you found this tip helpful, Check out our other OneNote Courses

 

 

Microsoft PowerPoint Tip: Creating Themes

A great feature in Microsoft PowerPoint is the ability to design your own theme and layout. It allows the user to freely design their slides and deliver quality presentations that are suited to their business style.

 

To create your own personalized theme, simply open a new blank presentation and click on the View Tab. Now in the Master Views Group, select Slide Master.

 

Microsoft PowerPoint: Slide Master Tab

 

 

The Slide Master is the largest picture out of all the others in the slide thumbnail drop down. Other slide layouts are situated underneath the slide master.

 

Microsoft PowerPoint: Slide Master Tab

 

To allocate changes to the slide master/slide layouts, select the Slide Master tab, and try the following:

 

 

  •  Try a Microsoft PowerPoint: Theme optionscolorful theme and add special fonts and effects, or click Themes, and choose a theme from the gallery. Use the scroll-bar on the right to see more themes.

 

  • To change the background, select Background Styles, and choose a background from the drop down menu.

 

  • To add a placeholder (add’s  text,  pictures, chart, videos, sound, and other objects), select the Insert Placeholder option in the tool bar , now select the option you would like to add into the slide.

Microsoft PowerPoint: PlaceHolder Tab

 

We hope you found this tip helpful. For any other tips on PowerPoint, Check out our PowerPoint Tips

 

Also, Get Social! Follow us on Facebook and Twitter