Media Tek Training Solutions logo
Microsoft Partner Network
Call us: 01943 870 638

Blog Excel Tips

  Excel Blog Tips & Tricks

To subscribe to our Excel newsletter, please contact us.

By M RHODES 23 May, 2017

Clearly, many of us are very busy in our daily working lives. Pressure brought on by time constraints, can be all too familiar in the office environment. 

In Excel, we may be busy entering and verifying data in order to produce timely reports etc. But frequently, people aren’t aware of the big picture in Excel. By that, I mean features that can really save you time and boost productivity. Such as, Excels form tool for speedy data entry, creating simple time-saving macros, using styles to quickly format worksheets and charts. One of the comments I hear most often during training sessions is “This is going to save me so much time!”. 

If what you learn in training, saves just 10 minutes per work day, that translates into a full working week per year!  

Saving time, increasing efficiency, boosting productivity – interested?

By M RHODES 01 Sep, 2016
During my career, I've been privileged to conduct training for hugely successful businesses who achieve incredible profits. Obviously, not all of us work for or are going to start up billion dollar companies. But there are things we can learn from them and implement, regardless of the size of our organization.
 
When you interact with these companies, you quickly realize that they will always strive to use the latest technology and that their staff will be trained to leverage that technology to the maximum. In other words, they have a data culture. This can even be something, such as adopting the latest versions of familiar apps like Microsoft Office.
 
So clearly, a data culture is all about working smarter, but here is how Microsoft define it: "A vision that can increase your business's value. That's because employees armed with data are known to make better decisions, improve customer experiences, and improve your bottom line."
 
Here are a few tips to foster a data culture:
 
Convert Data into Intelligence
These insights can come in the form of actionable intelligence. Microsoft now incorporates Business Intelligence tools right into applications such as Excel.

Make Data Everyone's Business
With the right tools, insights can come from anyone, anywhere, at any time.
 
Encourage professional development
In order to make employees more data literate, businesses can ramp up training about how data works, what it means, and what can be done with it, so that it can improve business profitability.
 
To quote Microsoft CEO Satya Nadella, "In a data culture, the entire effectiveness of an organization can elevate."


By M RHODES 01 Jul, 2016

During training, I'm sometimes asked about opening different Excel workbooks on two monitors. My response is, that doing this in Excel 2010 can cause problems, as you have to open two instances of Excel. This can lead to issues executing functions and running macros.

However, there is a new feature built in to Excel 2013 called the Single Document Interface (SDI). This was developed to allow people to work with multiple workbooks on two monitors. You can now easily put one workbook on the left monitor and another on the right monitor. Both workbooks will be running in a single instance of Excel and each workbook has its own ribbon, formula bar etc. This increases functionality, so you can easily copy and paste and perform other operations between the two workbooks. Here are the steps:

Assuming you have the monitors set to 'Extend these displays' in the Display Control Panel.
Open Excel and the two workbooks.
Simply drag one workbook to the left monitor and the other to the right monitor.
As mentioned, each workbook has its own ribbon.

Note: You can also view two worksheets of the same workbook on two monitors. This is done by going to the View Tab/New Window and dragging each window accordingly.

By M RHODES 05 Apr, 2016

Quite often in Excel, I see people struggling to use pivot tables or grappling with over complicated formulas. Why is this? The number one reason, is that the data on the spreadsheet is in the wrong format. Wherever possible, Excel data should be formatted as tabular data. That is, in a table where each row represents one record and each column one field - as illustrated above.

When you start with this level of detail, it's then easy to summarise your data with pivot tables and any formulas should be relatively straightforward. So a simple concept - Excel was designed to work best with tabular data.

By M RHODES 02 Feb, 2016

Those of you who have attended Media Tek's training sessions will know that I'm always going on about making Excel more visual, which is in line with Microsoft's philosophy. Sparklines were introduced in Excel 2010 and are great for identifying trends rather than looking at a mass of numbers. As you can see above, they are being used to compare sales. The Sparklines are also showing the high point and low point for each salesperson. Because sparklines fit in a cell, they're ideal for use in Excel Dashboards too.

Creating Sparklines

The first thing you should do, is select the cell or cells you want them inserted into, then go to the Insert tab > choose the Sparkline type you want.

This will open the 'Create Sparklines' dialog box and you can select the range of cells containing the data for your Sparklines. There are 3 Sparkline types to choose from: Line, Column and Win/Loss.

Formatting Sparklines

When you click on a Sparkline it will activate the Sparkline Tools/ Design tab on the ribbon. Here you can edit the location and source data, add markers for various points and choose from pre-set Styles etc. 

So there you go, Sparklines a quick and easy way to visualize your data in Excel.


By M RHODES 23 May, 2017

Clearly, many of us are very busy in our daily working lives. Pressure brought on by time constraints, can be all too familiar in the office environment. 

In Excel, we may be busy entering and verifying data in order to produce timely reports etc. But frequently, people aren’t aware of the big picture in Excel. By that, I mean features that can really save you time and boost productivity. Such as, Excels form tool for speedy data entry, creating simple time-saving macros, using styles to quickly format worksheets and charts. One of the comments I hear most often during training sessions is “This is going to save me so much time!”. 

If what you learn in training, saves just 10 minutes per work day, that translates into a full working week per year!  

Saving time, increasing efficiency, boosting productivity – interested?

By M RHODES 01 Sep, 2016
During my career, I've been privileged to conduct training for hugely successful businesses who achieve incredible profits. Obviously, not all of us work for or are going to start up billion dollar companies. But there are things we can learn from them and implement, regardless of the size of our organization.
 
When you interact with these companies, you quickly realize that they will always strive to use the latest technology and that their staff will be trained to leverage that technology to the maximum. In other words, they have a data culture. This can even be something, such as adopting the latest versions of familiar apps like Microsoft Office.
 
So clearly, a data culture is all about working smarter, but here is how Microsoft define it: "A vision that can increase your business's value. That's because employees armed with data are known to make better decisions, improve customer experiences, and improve your bottom line."
 
Here are a few tips to foster a data culture:
 
Convert Data into Intelligence
These insights can come in the form of actionable intelligence. Microsoft now incorporates Business Intelligence tools right into applications such as Excel.

Make Data Everyone's Business
With the right tools, insights can come from anyone, anywhere, at any time.
 
Encourage professional development
In order to make employees more data literate, businesses can ramp up training about how data works, what it means, and what can be done with it, so that it can improve business profitability.
 
To quote Microsoft CEO Satya Nadella, "In a data culture, the entire effectiveness of an organization can elevate."


By M RHODES 01 Jul, 2016

During training, I'm sometimes asked about opening different Excel workbooks on two monitors. My response is, that doing this in Excel 2010 can cause problems, as you have to open two instances of Excel. This can lead to issues executing functions and running macros.

However, there is a new feature built in to Excel 2013 called the Single Document Interface (SDI). This was developed to allow people to work with multiple workbooks on two monitors. You can now easily put one workbook on the left monitor and another on the right monitor. Both workbooks will be running in a single instance of Excel and each workbook has its own ribbon, formula bar etc. This increases functionality, so you can easily copy and paste and perform other operations between the two workbooks. Here are the steps:

Assuming you have the monitors set to 'Extend these displays' in the Display Control Panel.
Open Excel and the two workbooks.
Simply drag one workbook to the left monitor and the other to the right monitor.
As mentioned, each workbook has its own ribbon.

Note: You can also view two worksheets of the same workbook on two monitors. This is done by going to the View Tab/New Window and dragging each window accordingly.

More Posts
Share by: