Microsoft Excel is the most used spreadsheet tool in the world. Learn more about this online course This certificate includes three courses that can also be taken individually: Advanced Excel, Intermediate Excel and Mastering Microsoft Excel. The Mastering Microsoft® Excel® course focuses of providing students with a solid foundation on the many features and applications of the Microsoft Excel spreadsheet program. Excel, being a vastly versatile and widely used tool in the modern workforce, is a skill integral to success for students planning for any business-related career. Mastering Excel Formulas VLOOKUP Mark Moore. 3.6 out of 5 stars 32. Excel Conditional Formatting: Tips You Can Use Immediately To Make Your Data Stand Out (Data Analysis With Excel Book 3) Scott Hartshorn. 4.3 out of 5 stars 39. And since nobody ever said Excel was easy, we’ve collected our favorite strategies for improved performance. 1.) Navigate Excel like a pro. Sure, a mouse and a trackpad are standard fixtures on most modern desks. But if you want to really supercharge your use of Microsoft Excel, learn keyboard shortcuts.

One of the main ways to master Excel is to be accomplished at writing formulas. These are the muscles of Excel. From performing basic calculations in cells to using more advanced formulas - you will stand head and shoulders above the rest if you have this skill.

In today’s fast-paced digital age, it’s almost required that we work more efficiently while utilizing technology to solve complicated problems. And no computer program does a better job of that than Microsoft Excel.

First introduced in the mid 1980s, many business experts consider Excel one of the greatest pieces of software ever developed — in 2013, PCMag called it “the first and only choice for manipulating numeric data.” And with huge chunks of the modern digital economy now reliant on the program, mastering its intricacies is a surefire way to boost productivity, enhance efficiency, and impress co-workers and employees.

Given its prominence in the software world, there are countless ways to turbocharge your Microsoft Excel experience, some of which are baked directly into the program’s interface and some of which are available as add-ins and plug-ins. And since nobody ever said Excel was easy, we’ve collected our favorite strategies for improved performance.

1.) Navigate Excel like a pro.

Sure, a mouse and a trackpad are standard fixtures on most modern desks. But if you want to really supercharge your use of Microsoft Excel, learn keyboard shortcuts. For instance, holding down Ctrl while tapping the up, down, left, or right arrows will select every cell all the way to the end of the sheet. Flip through worksheets by holding Ctrl while pressing either Page Down or Page Up. Turn a range of numbers into currency, complete with a dollar sign and two decimal places, by pressing Ctrl + Shift + $ at the same time. You can also turn a number into a date (day, month, and year) by pressing Ctrl + Shift + #.

2.) Apply similar formatting to multiple sheets at the same time.

This critical task is a breeze with Excel’s grouped worksheets feature. Click the tabs of the sheets you want to group together while pressing Ctrl and they’ll all turn white; while the sheets are grouped, any formatting entered on one sheet also shows up in the others. After formatting grouped sheets, make sure to click on Tabs to ungroup them before entering or removing data.

3.) Insert Excel data into Microsoft Word or PowerPoint.

If you’ve ever tried this before, you might laugh. But transferring data from a spreadsheet to a text document is possible. Just be careful to click the clipboard that shows up after you paste to decided whether you’d like to keep the original Excel formatting or have the pasted data match the format of your existing Word or PowerPoint document.

4.) Simply select columns and rows.

If your data is in a table and you need to refer to an entire column in a new formula, click on the column name. (Pro tip: make sure your cursor looks like a down arrow before you click on the column name.) Other handy selection shortcuts include Shift + Spacebar, which selects an entire row, and Ctrl + Spacebar, which selects an entire column.

5.) Insert rows and columns with ease.

A good Excel spreadsheet is always ready to grow. Quickly adding rows and columns can be done in a variety of ways: clicking Insert > Rowsor Columns; right-clicking on rows or columns and choosing Insert; or pressing the Ctrl and + or Ctrl + Shift and +

6.) Turn columns into rows (or vice versa).

If you have data organized into columns that needs to be converted into rows (or the other way around), don’t worry. Copy the original block of cells, then right-click on the destination cell and select Paste Special,then Transpose.

7.) Use simple calculations to save time and energy.

Excel’s formulas can be legendary for their complexity, but simple calculations can often give you the most immediate boost in productivity. To add, use the + key; to subtract, use the – key; to multiply, use the * key; and to divide, use the /

8.) Add a summary row to a table.

You can add a summary row to a table in the Design ribbon (using Windows) or the Table ribbon (on a Mac) by checking Total Row. You can then choose from a variety of summary statistics: not just total sum but total count, average, standard deviation, more. Placing the info in a total or summary row means it’s attached to your table but will stay in the bottom row regardless of how you then might choose to sort your table data. This can serve as a huge timesaver if you’re doing lots of data exploration.

9.) Use AutoFill.

This works great for repetitive tasks: filling in dates, times, or other values that follow a sequence. Start with the first entry, then move the cursor over the lower right section of the final cell, which brings up a fill handle. Once it changes to a plus (+) sign, click and select all the cells you need to fill with your chosen pattern. You can even go up a column or left and right on a row.

10.) Hide data in protected worksheets.

If you want to share some data with other users but not all of it, consider hiding it. The easy way is to right click and select the Hide function. But you can be even safer by using the Format Cells function by clicking Home > Font > Open Format Cells > Number Tab > Custom > Type. That way, your data can stay truly safe.

Whether you’re a seasoned Excel pro on the hunt for fresh usability tricks, or a new user scrambling to make sense of it all, these tips should beef up your program experience. Want to know more about the Microsoft Office productivity suite? Curious whether your office is ready for an upgrade that makes your technology life more productive and efficient? Contact CMIT Solutions today. We’re here to make hardware and software work for your business, not against it.

Microsoft Excel is one of the world's most popular business software packages. It is incredibly powerful, with more features added to it continuously.

You may, therefore, have a desire to learn how to master Excel so that you can work quicker, easier and with more efficiency.

Mastering Excel Pivot Tables

This article will guide you through 11 of the best tips and tricks to help you harness the power of Excel and impress your boss and work colleagues.

How to master Excel quickly in 11 steps

1. How to navigate the interface

A good start is to be efficient at navigating the Excel interface. Let’s start with the basics.

When typing data into Excel you can use the Tab key to move to the next cell in the column to the right.

The Enter key can be used to move to the cell in the next row down. If you have been using tab to move through the columns, pressing Enter will take you one row down and back to the cell in the column you started in.

Pressing the Ctrl key and the Up, Down, Left or Right directional arrow keys together will take you to the last used cell in that direction.

This is very useful to navigate large lists.

Pressing the Ctrl and the Home key together will take you to the first cell in your data range.

2. Learn some useful shortcuts

The best way to speed up your day-to-day Excel work is to learn some useful shortcuts both with the keyboard and your mouse or touchpad.

If you are a beginner, then start by learning the general keyboard shortcuts. Good ones to learn include Ctrl + C and Ctrl + V to copy and paste. And Ctrl + Z to undo the last action.

As you begin to use these shortcuts in your daily work, push yourself to learn more.

Two of my favorite shortcuts are Ctrl + ; to enter today's date in a cell. And double-clicking the fill handle (the square in the bottom right corner of the cell) to copy to the bottom of a list.

Pes 2012 patch for pes6 free download programs. Excel has over 500 keyboard shortcuts and a whole bunch of useful tricks for speeding up how you work.

Although you won’t need them all, learning some to make your Excel work easier is a great idea.

Ready to start using Excel shortcuts?

Download our printable shortcut cheatsheet for Excel. Microsoft notes.

3. Freeze panes

This wonderful feature keeps your headings and other labels visible at all times as you scroll around the spreadsheet. It is an essential Excel skill to know.

The headings for a spreadsheet are usually just in the top row. So to freeze this area, click View > Freeze Panes > Freeze Top Row.

In the image below, we are looking at row 1259 but the headings are still visible on screen.

You can freeze as many rows, and columns, as you need.

Click in the first cell after the rows and columns you want to freeze. For example, if you want to freeze 3 rows and 1 column, click on cell B4.

Click View > Freeze Panes > the first Freeze Panes option.

Now when you scroll down, the first 3 rows remain visible. And if you scroll to the right, column A remains visible.

4. Learn how to master Excel formulas

One of the main ways to master Excel is to be accomplished at writing formulas. These are the muscles of Excel.

From performing basic calculations in cells to using more advanced formulas - you will stand head and shoulders above the rest if you have this skill.

If you are new to formulas, start with creating basic calculations that add, subtract, multiply and divide values.

Then begin to learn some of the more commonly used functions. These include SUM, IF, VLOOKUP, COUNTIF, and CONCATENATE.

Mastering Excel Formulas And Functions

When you are comfortable writing formulas, you can do almost anything. You can even use formulas within Conditional Formatting rules, charts and other Excel features to make them more powerful.

Check out some of our favorite Excel formulas and functions.

5. Create a simple drop-down list

A simple drop-down list on a spreadsheet can make entering text much easier, and more importantly ensure it is accurate.

To create a drop-down list;

1. Select the range of cells you want the list to appear in.

2. Click Data > Data Validation.

3. Select List from the Allow list.

4. Then either type the list items into the source box separating each one with a comma. Or select a range of cells that contain the list items.

Now you have a drop-down list, making data entry simple and reliable.

6. Visualize key data with conditional formatting

Conditional Formatting is one of the most popular features of Excel. It helps the user get a quick understanding of the data they are looking at.

You can create simple conditions to automatically format cells if a target is reached, a deadline has passed or maybe sales have decreased below a certain threshold.

For a quick example, if a value is larger than 300 we want to change the cell color to green.

1. Select the range of cells that you want to apply Conditional Formatting to.

2. Click Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

3. Enter 300 and select the formatting you want to apply.

A green fill and font color are used here, but there are lots of formatting options available.

With conditional formatting, there is also the ability to apply data bars and icon sets. These visuals can be very effective. The image below shows data bars applied to the same range of values.

7. Flash fill

Flash Fill is an awesome feature that quickly manipulates data. This feature can greatly reduce the time taken doing regular data cleansing tasks, that previously we relied on formulas and macros to do.

Take, for example, the list below. We want to extract the name from the list in column A.

Enter the first name in cell B2 and then the second name in cell B3 and as you type Flash Fill picks up the pattern in your behavior asks if you would like it repeated over the rest of the rows. This is amazing!

Press Enter and voilà the data is extracted.

There are several ways of running Flash Fill. You can also find the button on the Data tab of the Ribbon, and you can operate it with the shortcut Ctrl + E.

8. Summarize data with PivotTables

Mastering Excel Youtube

PivotTables are one of the most amazing tools in Excel. They make summarising large datasets as easy as 1, 2, 3.

A PivotTable could be used to break down a large list of sales to see the sales by region or even sales of particular products in each region.

They are extremely powerful, but also simple to use. A PivotTable is a tool to make creating reports more straightforward and doesn't require writing any complex formulae.

To create a PivotTable;

1. Click in the list of data you want to summarize.

2. Click Insert > PivotTable.

3. In the Create PivotTable dialog, ensure the range being used is correct and specify whether you want the PivotTable or a new or existing worksheet.

4. Drag and Drop fields from the field list into the four areas to create your PivotTable.

In the image below, the years is in Columns, the product category in Rows and the total sales value in the Values area.

The Values area is where the calculations such as sum, count and average are performed.

Mastering Excel Pdf

The calculation can be changed by right-clicking a PivotTable value, selecting Summarize Values By and selecting the function you want to use.

There is plenty more that PivotTables offer but that is beyond the scope of this article.

With PivotTables, I encourage you to grab some spreadsheet data, insert a PivotTable and start to drag fields into the areas and explore the options available. It really is a tool that you learn by doing.

9. Protect Excel data

Excel features such as PivotTables, formulas and Conditional Formatting will only work if the data is accurate.

A few of the tips in this article demonstrate ways to maintain this accuracy including creating drop-down lists and using Flash Fill. Protecting your Excel data is another important step to take.

Mastering Excel Book

Excel has a few different types of protection that can be applied, but the most popular one is to protect the sheet.

Protecting a sheet is performed in two stages. Firstly you specify which cells on the worksheet, you do not want to lock. This ensures that users can still edit these cells. Then you apply the protection.

1. Select the range(s) of cells that you want to unlock (hold down the Ctrl key to select multiple ranges at the same time).
2. Press Ctrl + 1 to open the Format Cells dialog.
3. Click the Protection tab, uncheck the Locked box and click Ok.
4. Click Review > Protect Sheet.
5. You can enter a password for extra security, but it is optional (this is typically omitted as you are normally protecting against accidents and a password is unnecessary). Check the boxes of any functionality users will need to perform and click Ok.

The unlocked cells can still be changed, but with limited functionality. If you try and edit a locked cell, a message will appear telling you that the sheet is protected.

10. Power up with Power Query and Power Pivot

Having knowledge of these two Power tools will take you beyond the regular user.

Power Query is a tool used to import and shape data ready for analysis. It is found on the Data tab of the Ribbon.

With Power Query, you can import data easily from a variety (and constantly evolving) of places including CSV, the web and from a folder.

The Power Query Editor then provides a user-friendly environment to perform a number of cleaning and shaping operations such as splitting columns, formatting, removing duplicates and unpivoting data.

Power Query uses a language called M. This is difficult to master and fortunately not needed for 99% of Excel users. The editor provides everything that a user would need.

Every edit that is made is stored as a step and can be refreshed in the future with the click of a button.

Power Pivot (also known as the data model) is a tool that enables the storage of huge volumes of data. Storing data in Power Pivot enables us to go beyond the physical limitations of Excel.

In Power Pivot you can also create relationships between multiple tables of data and write functions known as DAX function.

The ability to create these models and use DAX provides far more power than you can get more a spreadsheet.

This is an important tool to understand for anyone involved in analyzing huge quantities of data or needs to perform complex analysis.

Read our 10 most useful Power Query commands to help you on your way.

Bonus: Check out Ken Puls' lesson from his Power Pivot course which explains what Power Pivot is, and how it can streamline your workflow with Excel. Download isa server 2006 standard edition.

11. Create Macros with VBA

Macros enable you to automate repetitive tasks to supercharge your productivity.

Mastering Excel Project 2e Plants

These tasks can be complex, but for most users, they are reasonably simple tasks that are repeated often. Macros provide a way to perform the tasks quicker and more reliably.

You can get started with macros by recording yourself performing Excel tasks. This will generate the VBA code and produce a macro.

You can find the macro recorder on the View tab of the Ribbon. It is the last button.

Recording macros can save you and your colleagues a great deal of time.

If you want to take things further, you can learn Excel VBA and edit your macros.

With VBA you can make your macros do things way beyond recording and create functionality that Excel does not offer itself.

To see the VBA code created by a recording;

Click View > Macros, select the macro in the list and click Edit.

Want to master Excel today?

Of all the tips on how to master Excel, the best way is to take a structured online Excel course that teaches you everything you need to know. Practice alongside a Microsoft MVP and learn the latest skills to boost your workplace efficiency today! If you're not ready to take a deep dive just yet, you can start with our quick and free Excel in an Hour crash course right now.

Free Excel crash course

Learn Excel essentials fast with this FREE course. Get your certificate today!