ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

5 Unknown Excel Features To Make Your Work Easier

Updated on October 7, 2014

Microsoft Excel is an extremely powerful and vast program, and because of this many of its features, new and old, can go unnoticed.

Yet some of these features could have saved you hours of time and aggravation, and improved your spreadsheet greatly.

Here is a list of the top 5 features of Excel (in my opinion), that people are not aware of.

Go To Special

I begin with a feature that has been around for many years and inspired this article. I demonstrate some techniques that require this feature on my courses, and very rarely are people aware of it’s existence.

Go To Special highlights cells on a spreadsheet, or in a selected range, that meet certain conditions. For example, it can be used to highlight all the blank cells, all those with formulas or those that contain Data Validation rules.

This feature can be used to easily delete blank rows in a range, change the colour of blank cells, or to protect all the cells containing formulas.

Go To Special can be found by clicking the Find & Select button on the Home tab of the Ribbon.

The video below shows how Go To Special can be used to delete all the blank rows in a list.

Custom Views

You can save your own views in Excel as a Custom View. A custom view includes any print settings, filters and also hidden columns that you have applied.

It provides a fast way for you to switch to a specific filter that you regularly do, or a view that you often print.

It can save much time messing around with unhiding columns, applying different column filters and modifying print settings.

To save a custom view;

  1. Create the view by applying the required filters and print settings.

  2. Click the View tab on the Ribbon and then Custom Views.

  3. Click the Add button, enter a name for your view and click Ok.

You can then apply the view when needed by opening the Custom Views dialog and clicking Show.

It is recommended to create a view without these settings applied, which you might call normal. This provides and easy way of resetting the filter, hidden columns and print settings.

Formatting a Range as a Table

This feature was introduced with Excel 2007 and has been improved in every version since.

There are many benefits to formatting your range as a table. These include;

  • It creates a dynamic range. If more rows, or columns, are added to the table it will automatically expand. This means any PivotTables or formulas using that table will be using the correct range.

  • It change the column headers (A, B,C, D etc) to the name of the field/column as you scroll down the worksheet. No need for Freeze Panes on your header when it is formatted as a table.

  • Provides consistent formatting for the range including formatting alternate rows for readability. If more rows are added to the table they are automatically formatted.

  • The table can be named for easy referencing from any sheet.

  • It provides structured references for your formulas. This makes your formulas easier to write, and to also read and understand them at a later date.

Take the following examples of a SUMIFS function. One using standard cell references and one using table references.

Using standard cell references

=SUMIFS(Sheet1!$J$2:$J$2156,Sheet1!$D$2:$D$2156,C5,Sheet1!$K$2:$K$2156,D5)

Using a table’s structured references

=SUMIFS(Orders[Total],Orders[Country],C5,Orders[Sales Rep],D5)

My creating sports league tables and tournaments in Excel online course contains some complex formulas that reference across sheets. Tables are used for all the ranges and work as a great example of how they can simplify the process.

To format your range as a table;

  1. Select the range of cells you want to format.

  2. Click the Format as Table button on the Home tab of the Ribbon and choose the style you wish to use.

  3. Confirm the range of cells is correct and click Ok.

Custom Formatting

Formatting cells in Excel is a big deal. It is very important that Excel understands the data you are using such as time, dates, currency and percentage. However it is also important that readers understand it.

Although Excel provides many popular formatting options at just a few clicks of a button, sometimes you need more than what they offer as standard.

Custom formatting offers a far more extensive list of formatting options including the option to format negatives values on a sheet in a red font.

It also allows you to create your own formatting. So if you have a specific format that a part code, employee ID or a date needs to be entered, then custom formatting is the place to go.

Create Your Own KG Format

Flash Fill

Flash Fill is a new feature to Excel 2013, but is so good that it immediately makes it onto my list. It has to be one of the best additions to Excel in recent years.

Flash Fill is an extension of the standard, yet also awesome, AutoFill feature of Excel (you know, the small square in the bottom right corner of a selected cell).

It will look for a pattern in the data that you entered and repeat that pattern in all of the cells that you fill, or copy to. Flash Fill can be used to;

  • Convert the case of text e.g. lowercase to uppercase, uppercase to proper case etc.

  • Concatenate text from multiple cells into one.

  • Copy part of a cell into another such as part of a product code, or somebody's last name.

  • Substitute a character, or characters, in a cell with something else e.g. replace 12.03.2014 with 12/03/2014.

  • Remove unnecessary spaces from the beginning and end of text in a cell.

Watch this video showing 5 awesome Flash Fill examples.

What were your favourites?

Which of these awesome Excel features do you love the most?

See results

What Excel Features Do You Recommend?

What Excel features can you not live without? Are there some Excel tools or shortcuts that you know that you find other users are not aware of.

Please share your hidden skills using the comments below.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)