Category Archives: Excel Training

How to share files in Excel

Collaborate more easily by sharing an Excel workbook

It is not uncommon with Excel to have multiple people requiring access to the same file. Whether the file in questions is a sales report, marketing budget, or time-sheet for example. This is where the Share Workbook feature in Excel comes in handy, here we’ll look at how to share files in Excel and things to watch out for.

The big incentive for sharing an Excel file amongst people is that there is just a single version in use. Not taking this approach entails all sorts of challenges, from having to manage multiple (and ever changing) versions of the same file through to organising how to merge all the data into a single file. By sharing, everyone is on the same page/workbook!

Step 1 Create or open an existing spreadsheet that you wish to share.

Step 2 Once open, go to the Review tab and select the Share Workbook.

how to share files in Excel

In the Review tab select the Share Workbook

Step 3 Now the Share Workbook dialogue box will be open. Make sure the Editing tab is open and then click on the box to “Allow changes by more than one user at the same time” (this is where you will also be able to view who else is using the shared file). Finally click OK to save the changes.

Select to share a workbook

Step 4 The file then needs to be saved to a location that others will be able to access. This could be a shared folder, network drive, or even a OneDrive (for Excel 2013 users).

About advanced sharing features

You may have noticed the Advanced tab on the above Share Workbook dialogue box. Here is a quick overview on what these advanced features do:

Advanced options for sharing a workbook

Track changes: If you wish to track revisions then select the Keep Change History option. The fewer days that you select the smaller the size of the change log.

Update changes: If the Excel file in question is in regular use it makes sense to set up an automatic save. You can even be notified of what changes are being saved.

Conflicting changes between users: Take an instance where the same cell has been changed by two users, you can either have the option to decide which change gets saved or simply set it so that only saved changes have final say.

Include in personal view: An option to include any set filter and print views that other users may have applied.

Sharing does limit Excel features:

When sharing you can: When sharing you can’t:
Insert columns/rows Create a table
View existing charts Can’t create new charts
Use existing conditional formating but not edit Merge cells or split merged cells
View existing Macros with limitations Group or outline data

 

The Share Workbook feature in Excel is a really useful way to easily collaborate between different users and not have the headache of managing multiple file versions. Yes it does need some consideration in terms of certain limitations of Excel features and ensuring everyone can access the file. It may not be a bad idea to actually keep a spare sheet in the workbook with some sharing guidelines for all users.
With Excel 2013 the options to share are even more varied considering that you can share a file for access across multiple platforms and devices (such as iPads).

More resources on how to share files in Excel

How to share a file in Excel 2010.
Read more

How Getting Together Is Now Even Easier With Excel.
Read more

Use a shared workbook to collaborate.
Read more

How To Split A Stacked Chart In Excel

How to create AND split a stacked chart in Excel

Amongst the many charts available in Excel, some of the most popular are column charts, and the main variants being clustered and stacked. We’ll look at how to split a stacked chart in Excel, and to do this let’s start by creating a basic column chart.

Creating a column chart

In this example we are looking at regional sales data.

Sample sales data

Let’s create a clustered column chart of the above data.

Step 1 Select the range of data (as above, which will be A1:B4).

Step 2 Now select chart type, and “Clustered Column” from Charts options on the Insert ribbon.

a basic clustered column in Excel

Step 3 The following chart is now created.

regional sales in an Excel clustered column

This is all well and good we can compare regional sales performance, but it’s not so easy to compare how well each region performed against total sales. That’s where stacked column charts come into their own, let’s see how to build one.

Creating a stacked column chart

Step 1 Simply select the above chart then choose “Change Chart Type” from the Design ribbon.

change an Excel chart tyoe

Step 2 In the “Change Chart Type” dialogue box just choose the “Stacked Column” option as below.

changing an Excel chart into clustered chart

We now have a single stacked column chart for regional sales.


Excel stacked chart for sales regions

How to split a stacked chart in Excel

Now supposing we want to compare North & South as a stacked column against West and East as a stacked column. This can be achieved by splitting the above stacked chart.

Step 1 We need to change the layout of our data. So in this case we are going to select the two cells containing the “West” and “East” sales figures, then move them one column to the right. See below.

preparing Excel data for split stacked chart

Step 2 Now select the new range of data, in this case A1:C4, and as before when creating our original column chart but this time selecting the “stacked column” option. We end up with the following.

how to split a stacked chart in Excel

We now have our regional sales in a split stacked column chart.

By learning how to split a stacked chart in Excel you can now visualise data in a new and useful way.

More Excel chart related information:

How do you add a piechart and a bar / column chart on one worksheet?
Read more

How do I create a Floating Column Chart in Excel?
Read more

Excel 2013: 3 new ways to customise your charts

Present your data in a column chart

Using the Total cell style in Excel

Trying to review a spreadsheet with unformatted data can be a real eye sore, but by using the Total cell style in Excel you can quickly give your data more context.

An example of using the Total cell style in Excel

Below we have a list of sales figures for several sales reps in a travel company. In just a few steps by applying the Total style helps the “Total” column of sales figures stand out.

Total cell style in Excel

Using a couple of Excel styles can make all the difference

1. Select the range of cells you wish to format. Goto the Home tab, then the Styles group. Then click on the “More” button the more button in Excel to expand the group. (for Excel 2013 & 2010 click on Cell Styles button, see the second screenshot below)

Using the Total cell style in Excel 2007

How to select Total option from style section

Using the Total cell style in Excel 2010

Selecting Cell Styles in Excel 2013 and 2010

2. Now within the Style options, under the section, “Titles and Headings” just select the Total option. The selected range of cells will now display the Total formatting.

In the example above, we also repeated these two steps for the row with “John”, and with one further twist, also included an additional style option under “Themed Cell Styles” which was to select a colour.

Tip: If you are experimenting with different styles you may wish to remove a cell style, to do this simply select the range of cells you wish to “reset”, then go to the Style group (within the Home tab) and select Normal from the “Normal, Bad, Good, Neutral” section.

how to remove a style in Excel

To remove a style, just hit the Normal button

What we have covered here is a simple demonstration of how using styles can lead to more engaging and professional-looking spreadsheets. You can even create your own custom cell styles which may contain multiple formatting options and can be a real time saver when dealing with similar spreadsheets.

Found this useful? Learn more about Excel cell styles:-

  • How To Use The Cell Styles Functionality In Excel 2010. Read more
  • How do you group a selection of styles on a workbook? Read more
  • A comprehensive guide to applying, creating and removing cell styles

 

 

How to calculate age from date of birth using Excel

An easy way on how to calculate the age from date of birth using Excel formula

Here we’ll look at how using the Today() function along with a less well known function, Datedif(), calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy demonstration of the usefulness of these two functions in an Excel formula to calculate age from a date of birth.

The following formula uses both these functions to achieve the desired result.

=DATEDIF(B4,TODAY(),”y”)
where “B4” is the actual cell containing the date of birth.

Here’s the formula in action.

How to calculate age from date of birth using Excel

Formula using the Datedif() & Today() functions

Tip: You can also express the age as months, or even days by simply changing the “y” in the above formula to “m” or “d” respectively.

There are many ways to achieve the same result and the example above is one such way on how to calculate age from date of birth using Excel.

How Excel stores dates:

Dates and time are some of the most common types of data that people use in Excel and the way that Excel stores dates is quite different to how we would imagine.

Instead of storing a date as day, month, year (01/01/1900, for example) Excel actually allocates a serial number (which is generated from working out how many days have elapsed since the year 1900 to that date).  And yes, any date before 1900, as far as Excel is concerned, just doesn’t exist! This is why if you don’t have the correct cell formatting for date data you get a number bearing no relation to the date!

Additional resources:

You can view answers to actual Excel users’ questions related to time and date on the following links:

http://www.microsofttraining.net/post-6898-calculated-age-person-each.html

http://www.microsofttraining.net/post-23813-calculating-ages-dates.html

For a more in-depth look at how to use dates and times in Excel:

http://support.microsoft.com/kb/214094

 

Pull Data, Not Teeth – The PivotTable Edition

In life as in business, we always strive to find the easiest ways of getting things done. Sometimes, however, the simplest methods involve cutting corners, obtaining short-term results but long-term headaches.

One of a long line of Excel features, the PivotTable is the best way to break your information down into more manageable chunks. In this post we’ll outline simple uses for the PivotTable along with a few tips to help you get the most out of your data.

excel pivottable

This data was converted into the PivotTable in 7 clicks

Uses for PivotTables

Anyone with a need to break down large data sets will find a use for the PivotTable. Sales managers, IT professionals, financiers and even marketers can save time and the get most from their information with this popular Excel feature.

A fantastic tool for summarising your data, the PivotTable has the ability to find hidden trends or relationships between data. Ok, so they’re not really hidden, but they may as well be surrounded by all that information. Sales managers rejoice: these complex tables can outline sales performance of team members over specified time periods, even down to products sold and of course much more.

Seminar

We empathise that the PivotTable has an off-putting name, but in truth, they are really easy to create and don’t even require a single formula to be written. To get started, just click any cell on your Spreadsheet and select PivotTable in the top navigation bar. Follow the prompts, tick a few boxes and complex tables will be created in front of your eyes.

As we touched on earlier, data can be easily transported, helping you to recognise trends within trends and look at your data more laterally. Again, for sales managers, one minute you’ll be able to see which team member has sold more coal to Newcastle in the past month, then you’ll be able to switch a few variables and see the trend of all products sold to Newcastle over the past few months.

Time saved is one of the major selling points for the PivotTable. These easy to create, complex tables become a powerhouse reference point for your every analysis requirements. From these tables, you’ll be able to create graphs and charts to better visualise your information. Ideal for presenting to colleagues and clients, you’ll look like a pro with just a few clicks of your mouse.

old-time-clock

PivotTable Tips

One benefit of grouping your data is that you can extract a subset of the grouped data onto a new worksheet. It’s really easy to do this too, just locate the group and double click in the total cell containing the data you’re interested in. Then all of the data that contributed to that total will be extracted onto a new worksheet.

Replace blanks cells with zeroes. When the PivotTable doesn’t have data for part of a row, you’ll get blank cells. It’s easy to get around this by right clicking any cell in the PivotTable and choosing options. In the layout and format tab in the format section, type 0 next to the field labelled “for empty cells show“.

a

Automatic updates mean that as soon as you change data in your original Spreadsheet, all you need to do is hit the refresh button and your PivotTable data will be bought up to date. Saving you time having to create a new table each time, Excel intuitively recalculates your figures. The larger the company or those with collaborative documents, the more useful this feature becomes. Imagine how many new PivotTables would need to be created if sales figures were updated daily.

Excel has some pretty good table styles and customisation options that help your data stand out and make it clearer to digest, not to mention brightening up your Spreadsheet. Change the colour and layout of your table using pre-set templates found in the top navigation bar.

Change the PivotTable summary function by right clicking inside the table and selecting “summarise data by” option. This allows you to look at the same data at a different angle. Quickly creating dynamic tables allows you to find those trends and even summarise them with a chart or graph for better reporting.

Sort your data by timescales quickly. Right click a date in the row field to group by months, years or quarters. Again this is a useful feature for measuring sales revenue and data change over time.

By employing these hints and tips, you’ll be able to save time and effort in reporting. What’s your favourite tip for helping you get the most out of your PivotTable?

Want to become an Excel expert? Attend one of Best STL’s training courses available London and UK wide.

Excel 2013: Use Timelines With PivotTable Data – MS Excel Training

new feature for microsoft office 2013See the timing of trends in your Excel 2013 PivotTable data with the new timeline feature. By default the data is split into months, though manipulation allows you to break this down into weeks, quarters, years or even days if you prefer.

If you make user of Slicers in your PivotTables and PivotCharts, you will be very comfortable with the timeline feature.

Timeline for PivotTables

  1. To add a Timeline, click in your PivotTable data area and from the Analyse ribbon select Insert Timeline from the Filter group.
  2. Select the date field required and your Timeline is now ready to be used to filter your date field chosen.
  3. You can change the specified dates by simply dragging each end of the timeline.

In the real world this could be a particularly handy tool on your performance dashboards. Once you’ve set everything up, you can then let users build their own view on the data.

Timeline for PivotTables

For more tips and features on Excel 2013 and other versions, browse MS Excel Training courses from Best STL, available London and UK wide. Speak to our a member of our team today to discuss the latest training offers along with your specific requirements. 

Excel 2013: 3 New Ways To Customise Your Charts – London Excel Courses

new feature for microsoft office 2013Whether you’ve decided to use a suggested chart to represent your data or already knew which one works best from the outset, a new toolbar in Excel 2013 allow you to customise your visualisation quicker.

Selecting the chart will automatically reveal two tool ribbons: Design & Format, both specifically designed to help you manipulate your Excel 2013 charts. Although the Chart Tools Layout tab no longer exists in 2013, the buttons it contained are still available, just in different places.

Excel 2013 charts

Three (new) buttons for chart formatting now appear at the top right corner of your chart; Chart Elements, Chart Styles and Chart Filters. Instead of digging through menus you can access these buttons overlaid on the chart.

Excel 2013 charts

 

Chart Elements

Add or Remove specific elements of your chart such as Data Labels and Gridlines. This way you can have as much or as little labelling and layout features as you desire.

Excel Charts 2013

 

Chart Styles

Change the colour and style of your chart with this simple formatting option. Scroll over each option to get a preview of how your new chart will look.

Excel Charts 2013

 

Chart Filters

Want to modify what data the chart includes? Previously you had to modify the data range in a fiddly way. Now you can hide and show data with the chart filters selecting them from the tick box menu, very similar to filtering data in a table. Customising your charts has never been so easy.

Excel Charts 2013

 

For more tips and features on Excel 2013 and other versions, browse London Excel courses from Best STL, available London and UK wide. With training levels ranging from beginner up to advanced and Excel VBA, there’s sure to be a course to suit your needs.

Microsoft Excel Training Courses 2013: Sparklines through Quick Analysis

new feature for microsoft office 2013Introduced in Excel 2010, Sparklines create charts within a cell. It can help to show one figure in context with others and illustrate trends. In this example a blank column is created for the sparklines before selecting the adjacent data.

In Excel 2013 the Quick Analysis feature has now brought Sparklines to the fore. Perfect for tracking performance over time, this new way of accessing them will give a quick insight into your data.

Sparklines in Excel 2013

How to: Highlight some Excel data in a table and look for the Quick Analysis tag to float over the bottom-right corner of your selection. Put your mouse over this icon to explore the options.

For more tips and features on Excel 2013 and other versions, browse Microsoft Excel training courses from Best STL, available London and UK wide. With our instructor-led training, you’ll be able to manipulate your data in ways you never thought possible, helping you save time and money. Choose your preferred course ranging from beginner to advanced level.

Microsoft Training Excel 2013: Audit your Spreadsheet with the Inquire Add-In

new feature for microsoft office 2013For users with the Office Professional Plus package, the Inquire add-in comes pre-installed on Excel 2013. Helping you to analyse, audit and review workbooks, this great new feature also has the potential to highlight errors and security concerns.

We recently discussed, how to take back control of your spreadsheets by reducing “fat finger mistakes” and auditing errors, which can have huge cost implications to businesses. The Inquire add-in is another great tool for preventing these issues from arising and escalating in Excel.

The new tool can be accessed via the Inquire Tab in the ribbon and includes a number of useful functions:

Worksheet relationship

  1. Workbook analysis
  2. Workbook relationship
  3. Worksheet relationship
  4. Cell relationship
  5. Compare files
  6. Clean Excess Cell formatting
  7. Workbook passwords

These seven functions are simple to use, many providing visualisations to help better understand the information.

We all know Excel for being data driven, however, visualising things often makes this mass data easier to understand, take for example recommended charts & graphs or all new quick analysis techniques.

A few of our favourite Inquire features include:

Cell Relationship in Excel 2013

  • Cleaning excess formatting, including formatting in blank cells which bloats file size and contributes to poor performance in Excel.
  • The ability to compare two workbooks, highlighting cells that differ. This is particularly useful during an audit.
  • Being able to visualise the relationships between cells. Understanding the audit trail of how a figure came to be is a great way of maintaining the integrity and accuracy of the data.

How to: Enable the Add-in through File > Options > Add-Ins, from the Manage drop-down choose COM Add-Ins > Go. Tick Inquire and click OK.

To use it select the Inquire Tab in Ribbon > Choose function

For more tips and features on Excel 2013 and other versions, browse Microsoft training Excel courses from Best STL, available London and UK wide.

Excel 2013: Complete data entry quickly with Flash Fill

new feature for microsoft office 2013Save time and effort with Flash Fill for Excel 2013. Just one in a long line of nifty improvements to help you work more efficiently.

Flash Fill for Excel 2013 notices patterns in your data entry and then auto completes the remaining, so there’s no need to use formulas or macros to do this. Data gets filled in automatically. Previously you would have to use variations on LEFT(), RIGHT(), MID() plus a few other supporting functions to do this.

For example the Flash Fill feature will automatically complete a list of surnames after starting to type the second surname. It recognises patterns and predicts what data to fill in for you. Start typing the initials and Flash Fill fills the list for you.

Flash Fill for Excel 2013

Top Tip: Flash Fill also recognises text case. After typing a name in upper case, clicking Flash Fill on the Data Ribbon fills all the rest of the names in upper case.

Flash Fill for Excel 2013

 

Whether you’re splitting out email addresses or stripping out surnames, Flash Fill is a very useful feature.

For more tips and features on Excel 2013 and other versions, browse One Day Excel courses London from Best STL, available UK wide.