Featured post

Four benefits of Moving to Office 365

By J. Peter Bruzzese

The recent global recession is still quite fresh in everyone’s minds. From an IT perspective it forced IT administrators to tighten their belts and hold off on upgrades of both hardware and software for quite some time.
The growth of “cloud” solutions (like Office 365) has provided IT teams with another option when considering new hardware and software. Here we’ll take a closer look at the features available, benefits and a key consideration when moving to Office 365.

What is Office 365, and why move to it?

The name is somewhat confusing because it appears to be the next flavor of the Office product suite (Word, Excel, PowerPoint, etc…).  In fact, Office 365 is many things, it is a cloud-based, hosted services solution for Office applications, email, collaboration, and more.
Exchange, SharePoint and Lync have moved from a traditional on-premise hosting to Microsoft datacenters (i.e.. their “cloud”). Instead of paying for the software upfront, you pay as a monthly/annual subscription for those services (hence the term Software as a Service or SaaS).

Office 365 has a variety of different packages to choose from and some of them offer the Office Suite as part of the subscription too.  If you choose one of these plans you can put the latest flavor of Office (Word, Excel, PowerPoint, etc.) on desktops/mobile devices.  And there are a variety of other services offered with Office 365 like OneDrive for Business, Yammer, Office 365 Video and more.

There are some key reasons why a move to Office 365 can benefit your organization from the perspective of an IT team getting ready to decide on-premises vs. cloud.  Let’s consider four benefits for moving to Office 365 and one key user consideration for IT administrators.

Four benefits of moving to Office 365:

No-Cost Server Infrastructure:  If you are looking to modernize your server-side solutions the cost of upgrading your existing infrastructure could be exorbitant.  Moving mailboxes to the cloud eliminates that concern and cost.  Microsoft will worry about the hardware and storage, and you can pay as you grow for easy scalability.

No More Upgrades or Fixes:  Much of the stress of an admin is handling fixes and upgrades for solutions to ensure they are patched properly and as secure as possible.  With Office 365 this is all handled for you.

Availability:  Microsoft provides a 99.9% SLA for availability.  Whereas on-premise you would have to have redundant servers on-site and additional servers in an off-site location to provide that level of availability, Microsoft has it all in place from day one to ensure your users have consistent access to their email and other services.

Services Offered:  As mentioned earlier, Office 365 is a mix of different services.  It’s quite amazing really when you consider all the different tools provided.  You can access the services through traditional means (Outlook connected to Exchange Online) or through browser-based connectivity (Yammer or SharePoint Online through your browser).

Upon logging into your portal you are offered a variety of tools to choose from, and the list is growing!  Note the options in the figure below.  OneDrive, Sites, Delve, Video, Office Online (Word/Excel/Point/OneNote) … offering a compelling price/features proposition.

collaborate_office_online

Office 365 App Launcher – benefits of moving to office 365

So we’ve covered four benefits of moving to Office 365, what could the key consideration be? Here it is…

Training

Take a user who has been on Windows XP with Office 2003 for the past 10 years.  Give them a new laptop running Windows 7/8 (v10 coming soon) and Office 2013.  Introduce the user to the new tools for communication and collaboration like Yammer, SharePoint, OneDrive for Business and so forth.  Instead of praising your team the users vent frustration at the dip in performance as they struggle to process all the new upgrades.

Don’t do that to your people.  Going from the menu structure of legacy Office into the new ribbon interface alone will be an overwhelming task for some.  If you have gone with Windows 8, the new interface is quite a step change (even for experienced users).

Support your people.  You want to give them the latest and greatest and you want them to experience the productivity boost that should come with new hardware and software possibilities.

But you must do so through training first.

Manage the change for users through classroom training, with all the benefits of hands on exercises under the guidance and support of an experienced trainer.  Or even run workshop sessions BEFORE you put that new laptop or desktop in front of them with all the new bells and whistles.

And then you might want to provide 24/7/365 support training through a video portal, which perfectly complements any classroom based training. An eLearning portal with an easy to use interface and short searchable learning clips helps users improve and maintain performance.

We are certainly living in exciting times.  Cloud solutions like Office 365 are offering small-midsize business an opportunity to have enterprise grade solutions right at their fingertips, without all the server side heavy lifting being placed on the IT admin.  The end-users will love the new possibilities opened to them and the increase in productivity through new communication and collaboration tools.  Exciting times indeed!

J._Peter_Bruzzese J. Peter Bruzzese (Office 365 MVP) is the CIO of ClipTraining.com, providers of short, task-based video training through an online portal solution.  He is an internationally published author with over a dozen titles to his credit about Windows/Office/Exchange/etc.  He is a technical speaker for Microsoft and others.  He writes a weekly column for InfoWorld entitled “Enterprise Windows” and is a strategic technical consultant with Mimecast.  You can follow JPB on Twitter @JPBruzzese and email him at jpb@cliptraining.com

For information on eLearning services in the UK, ClipTraining partner and UK distributor, Best STL, can be emailed at info@microsofttraining.net

Further reading

Office 365 Training in the ClipTraining Library
Classroom based MS Office training

36.5 Office 365 Features That Will Boost Your Productivity
What’s In & What’s Out In Office 2013
A well-trained I.T. team: Your company’s secret weapon

 

How to Delete Blank Rows in Excel

Quickly and easily delete blank rows and columns in Excel

It’s not uncommon to end up with blank cells or rows in an Excel worksheet. If you need to delete these to maintain accuracy, especially when calculating the average value, the quick way is to filter out all blank cells / rows and delete them with one click.

Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Now, the blank cells / rows can be deleted easily.

delete_cells_rows

Delete blank rows in Excel

Another way to delete blank rows is to first select blank cells inside the data using the Go To Special feature. It works as follows:

GTS

This tool is opened by pressing F5 or by clicking Home, Find & Select, Go to Special. In the Go to Special box, select the Blanks option, then click OK. You will notice that all blank cells in and around the data are now selected.

SelectedCells

At this point, to delete the empty rows, go to the Home ribbon and click the drop-down of the Delete button in the Cells group. In the drop-down list, click Delete Sheet Rows.

DSR

The data will now have no blank rows inside.

NBR

Use this method only for simple tables with a couple of dozens of rows that fit within one screen. The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.

Delete blank rows in Excel using COUNTBLANK

Use this method if you have a table with numerous empty cells scattered across different columns, and you need to delete only those rows that do not have a single cell with data in any column.

1

In this case we do not have a key column that could help us to determine if the row is empty or not. So we add the helper column to the table:

Step 1. Add the “Blanks” column to the end of the table and insert the following formula in first cell of the column: =COUNTBLANK(A2:C2).
This formula, as its name suggests, counts blank cells in the specified range, А2 and C2 are the first and last cell of the current row, respectively.

2

Step 2. Copy the formula throughout the entire column.

3

Step 3. Now we have the key column in our table. Apply a filter to the “Blanks” column to show only rows with the max value (3). Number 3 means that all the cells in a certain row are empty.

4

Step 4. Then select all the filtered rows and remove whole rows .
As a result, the empty row (row 5) is deleted, all the other rows (with and without blank cells) remain in place.

5

Step 5. Now you can remove the helper column. Or you can apply a new filter to the column to show only those rows that have one or more blank cells.
To do this, uncheck the “0” checkbox and click OK.

6

7

 Additional Resources

Deleting rows of Data

Delete cells, rows, or columns

Using the Advanced Filter in Excel

Refine your filter options with the Advanced Filter in Excel

For most filtering of data Excel AutoFilter is perfectly adequate. Here we’ll look at some benefits for moving beyond the AutoFilter by using the advanced filter in Excel.

A Recap of AutoFilter Benefits

For ease of use, it’s hard to beat the Excel AutoFilter feature. Just click the Filter command on the Excel Ribbon’s Data tab, and the filter is ready to go.

Filter

You can click the drop down arrows in the column headings, and use the check boxes to select the items you want in the filter. Or, use the Text, Date or Number Filters commands, for variations on the filters.

Autofilter

Another quick way to filter is to right-click on a cell in the list, then click Filter, and click Filter by Selected Cell’s Value.

Right-click

Benefits of using Advanced Filter in Excel

The Advanced Filter in Excel isn’t quite as easy to use but it does have some benefits that make it worth the effort.

With the Advanced Filter, you can apply multiple filter criteria to the whole database whereas with the other types of filters, you have to filter in a step-wise way, which means that when you have applied one type of filter to the data, there is less data available to run a second and third filter.

After you click the Advanced command on the Excel Ribbon’s Data tab, the Advanced Filter dialogue box opens. Fill in the details, and then click the OK button to filter the data.

advanced_filter_in_excel

Filter Data to Another Sheet

Another reason to use an Advanced Filter, especially when working in Excel, is that it’s a quick and efficient way to copy specific data to a different worksheet.

In the screen shot above, the option is selected to copy the filtered data to another location. You can use that option to quickly create a report for each department, or salesperson, and email the results.

If you select “Filter the list, in-place”, the results will be shown in the database.

Tip: If you wish to remove the filters, you need to click the Clear button in the Data ribbon because the dropdown filter arrows disappear when you apply advanced filters.

Create a List of Unique Items

Excel 2007 introduced the Remove Duplicates feature, but you can still use an Advanced Filter to create lists of unique items. The Remove Duplicates feature strips the duplicates out of the list that is selected, so you have to remember to work on a copy of the list, if you want to keep the original list intact.

With an Advanced Filter, no items are removed from the list, so you don’t risk losing any of your original data.

Complex Filters

With an Advanced Filter, you can create a criteria range on the worksheet, and see at a glance what the filter settings are. You can also create complex filters with AND/OR settings that go beyond what an AutoFilter can do.

For example, with an AutoFilter, you can select two specific customers, and two products for those selected customers.

AuFRes

With an Advanced Filter, you can create OR conditions between columns, such as Customer A OR Product B — you can’t do that in an AutoFilter!

AdFRes

How to create a criteria table for the Advanced Filter

1. Paste a copy of your data table’s header row into an empty section of your spreadsheet or into another blank sheet.

2. Then type in your search criteria below the appropriate headers. If you type criteria next to each other, as in the example below, it performs an AND filter, meaning it will show results meeting ALL requirements.

CritTab1

The criteria above will show people whose surnames start with C AND who work in Development. It will also show people whose surnames start with F AND who work in Production.

If you type criteria on different rows, as in the example below, the criteria will perform an OR filter, meaning it will show results which meet ANY requirement.

CritTab2

Wildcard searches

? (Question mark): Any single character.
For example, “Bl?ck” finds either “Black” or “Block”

* (asterisk): Any number of characters.
For example North* finds “North”, “Northeast”,
and “Northwest”.

*west finds “West”, “Northwest”, Southwest”, etc.

*a* finds the “a” anywhere in the text

Additional resources

The difference between Autofilter and Sort in Excel

Using filters in Excel

Using VBA to filter data

Filter by using advanced criteria