1. Advanced Excel Techniques Pdf Tutorial
2. Advanced Excel Training Free

One of the most useful techniques in Excel is to name ranges. A named range can simplify code as it is possible to refer to the name and not the cell references To create a named range we use the add method of the workbook’s names collection. The following code creates a named range called “NewName” on sheet2 of the active. Take your Excel data science skills to an advanced level. As data strategies become more pervasive, Excel will become even more popular for non-data scientists doing more work, more often, with data. Advanced Excel Guide Pdf; Excel Advanced Charting Techniques Pdf; Microsoft Excel Advanced: Participant Guide 10 Conditional Formatting Conditional formatting in Excel enables you to highlight. When we talk about best excel tips and tricks, colour coding takes centre stage. It’s very basic yet most important excel tip for both beginners & advanced users. The basic idea is to separate data input cells & formula cells in a report or model by defining and applying unique colours across the workbook. Download Free PDF. Download Free PDF. Advanced excel formulas and functions kd. Open a blank worksheet and try the following fill handle techniques:A. Type a #1 in A1. Drag down to A12. The traditional example of this is taking the student example a little further. In the figure below, (which is in the Advanced Excel.

Have you been looking for Excel Advanced Tips and Tricks to make you faster and save your time?

Look no further: in this article you will see 15 tips and tricks in Excel to save time and get known by your peers as an Excel ninja. So, why wait? Let’s get started!

You will save tons of time and make moves like a ninja once you master the keyboard shortcuts. Here are some of my favorites:

1. |Ctrl + A|: will select all of the data
2. |Ctrl + C|: will copy the selected data (or object)
3. |Ctrl + V|: will paste the copied data (or object)
4. |Ctrl + End|: will take you to the last cell of your data
5. |Ctrl + Home|: will take you to the first cell of your data
6. |Ctrl + Up Arrow|: will take you to the first cell of your active column
7. |Ctrl + Down Arrow|: will take you to the last cell of your active column
8. |Shift + Space|: will select row(s) of your active cell(s)
9. |Ctrl + Space|: will select column(s) of your active cell(s)
10. |Shift + Space|: will select row(s) of your active cell(s)v
11. |Ctrl + minus sign|: will give you Delete options

For a complete list of keyboard shortcuts, visit: http://bit.ly/1QljQSp

To quickly copy a formula down:

1. Hover to the bottom-right corner of the cell with the formula (you will notice that the cursor has turned to a thick black plus sign)
2. Double click the plus sign

When tip #2 doesn’t work because there’s a blank cell between your starting cell and the end cell of your range or you don¢t want to drag a formula down to 1000 rows, try this:

1. Go to the first cell that you would like to copy or fill in other cells
2. In the name box, type in the address of the last cell of the range, where you want to fill data or formula and hit |Shift + Enter|
3. Press F2 to edit the formula in your first cell
4. Then hit |Ctr + Enter|

You can see the name box highlighted in the red box. You will find this box right next to the fFormula Bar

To quickly total a column or a row, in the last cell, hit |Alt + =| (equal to sign).

To delete rows with duplicated data, follow these steps:

1. Select the range of the data that you want de-duplicated (usually, |Ctrl + A| works)
2. Click on the Data menu option from the menu ribbon
3. Click on the Remove Duplicates button
4. Choose whether your range has a header row
5. Hit OK

You often find the need to add leading zeros to a number and most likely the result value is a text format. For example, you may want to show the number 7893 as 0000007893, making the number a text value with a length of 10 characters. If your number is in A1 and you want to convert that to text with leading zeros with a maximum length of 10 characters, enter this formula in B1:

While printing a multi-page sheet, it is useful to repeat the header row(s) on every page. To do so,

1. Click on Page Layout menu option on the riboon
2. Click on Print Titles button
3. Select the row(s) you want to repeat at the top in Rows to Repeat at Top box

For sheets with large number of rows, it is useful to give names to ranges so that you can refer to these names in your formulas without clicking and selecting long ranges. To quickly give names to your ranges:

1. Click on the Formulas menu option on the ribbon
2. Click on the Create from Selection button
3. Select the ranges to give names from usually Top Row works well as this row is the header row

To speed up copying only values and not the formulas, use this keyboard shortcut sequence:

1. |Ctrl + A| to select the whole range of data (or select your range with the help of the mouse)
2. |Ctrl + C| to copy the data
3. in your destination, press |Alt + E|, then |S|, then |V|, and then |Enter|

The whole sequence is:

After you practice this a couple of times, when you do this in front of your peers, without a doubt they will be in awe of your new ninja abilities.

Often you need to import data from the internet and you wish for a better way of doing so. Well, here is one way. Let¢s say you want to import the list of all time leaders of home run hitters in baseball from baseball almanac. Follow these steps to enjoy seeing this data in an Excel file:

1. Click on Data menu option from the ribbon
2. Click on From Web
3. In the browser window, enter the URL: http://www.baseball-almanac.com/hitting/hihr1.shtml
4. Hit the Go button on the browser window
5. Scroll down to the table of home runs
6. Click on Click to select this table check box
7. Click on Import
8. Click on OK

Only a ninja can import raw tables from the web to the Excel files!

To quickly delete current row(s), follow these two steps

1. Select the row(s) by |Shift + Space|
2. Delete the selected row(s) by |Ctrl + - (minus sign)|

To quickly delete current column(s), follow these two steps

1. Select the column(s) by |Ctrl + Space|
2. Delete the selected column(s) by |Ctrl + - (minus sign)|

Quick Tool provide aggregate statistics, such as Average, Count, Numerical Count, Max, Min, and Sum of the data from a selected range without entering any formula. To show these statistics in the bottom toolbar, right click on the toolbar and choose the desired statistic.

Often you have to translate or cross-walk a value, say a state code, to it a related value, in this case the fully spelled state name. You can write multiple, nested |IF|, but a ninja way is to use a |VLOOKUP| formula. The syntax of VLOOKUP formula is: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• In the above example, our lookup value is the state code and table array is a table with the state codes and their descriptions.
• The column index is the column that we want to return after matching a code
• The range lookup is an argument to search for exact matches (1 or TRUE) or approximate matches (0 or FALSE)

Let’s say, your customer IDs in Column A, their mailing state code in Column B, and in Column C you want to see the state spelled out. To do so, follow these steps:

1. Create a table of codes and their fully-spelled values in Column E and F
2. In |C2|, enter this formula: =VLOOKUP(B2,E3:F6,2,0)
3. Copy the formula down

Here’s how it looks:

Did you ever find yourself with the need to convert a number column to discrete ranges for easier summaries or graphs? Did you wish there was a better way? Well, there is: the |VLOOKUP| formula. You saw the syntax: The syntax of VLOOKUP formula is:=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) To convert numbers to ranges, you use the |range_lookup| argument with a value of |TRUE|, which will tell Excel to make an approximate match to the lookup value. We can use this to our advantage. Let’s say, you have some measure, such as, population, revenue, sales, # of units, etc, in Column A. You want to convert these measures to certain ranges. To do so, follow these steps:

1. Create a table of measures and their discrete ranges in Column F and G
2. In |C2|, enter this formula: =VLOOKUP(A2,F3:G8,2,1)
3. Copy the formula down

Here’s how it looks:

Did you have a lots of data in a text document and you wish you could easily copy that to Excel? Let’s say you have a Word document with some bullet points, like this:

• Vendor A: 5,000
• Vendor X: 9,900
• Vendor D: 10,000
• Vendor Z: 3,500

To copy and keep the desired formatting for easier manipulation, follow these steps:

1. Copy the data from your Word document
2. Paste the data in a blank Excel spreadsheet (say starting from cell |A1|)
3. Select column A
4. Click on the Data menu on the ribbon
5. Click on Text to Columns button
6. In the Convert Text to Columns Wizard — Step 1, select “Delimited”
7. In Step 2, check the “Other” box and enter |:| (colon) as the delimiter.
8. Hit Next/Finish and complete the wizard

Here’s how it looks: The text will be separated by your delimiter and data will be in separate columns

Suppose you have some text in a few columns. You want to join or concatenate these columns, but one of the column needs to be surrounded by double quotes. What to do in such as case?

So, let's say column A has first names, column B middle initials, and column C last names. You want the middle initials be surrounded by double quotes. You could escape the quotes by surrounding them with two more quotes. The formula will look like this:

=A2&''&B2&''&C2

It gets ugly soon. If you want to add spaces or any other characters, the formula becomes long and unreadable. A better option is using the CHAR formula. The CHAR formula shows a character for a code number. You can rely on ASCII tables to find out the character you want to concatenate. The ASCII number of 34 is a double quote character. (32 is a space).

So, our new formula will look like this:

=A2&CHAR(34)&B2&CHAR(34)&C2

This doesn't have spaces though, so we will add a space using CHAR(32):

=A2&CHAR(32)&CHAR(34)&B2&CHAR(34)&CHAR(32)&C2

It will look like this:

Often, you need to see a count of unique values in a range. While you can use other methods to delete any duplicate rows, a quick formula is sometimes all you need.

Let's say you have some text values in A1:A10 range. In this example, we have city names in this range. I repeated some cities for this example.

In another empty cell, in this case, in C1, we type this formula:

=SUM(1/COUNTIF(A1:A10,A1:A10))

But instead of hitting the regular enter key, we use Command + Shift + Enter on a Mac or Control + Shift + Enter on Windows. By doing so, we create an array formula. You should see the number 7 as the result of this formula.

Suppose you have some numbers or values in a range, and you want to count how many of them fall within a certain range. How would you find that count?

There are two ways of doing so:

A fixed method

Let's say our data is in the range A1 to A15. We want to count how many rows have values that fall between 50 and 80. In an empty cell, as shown in the image below, we enter this formula:

=COUNTIFS(A1:A15,'>=50',A1:A15,'<=80')

But now if you want to count something different, you will have to edit the formula. A better approach is a dynamic formula.

A dynamic method

Let's say we store our from and to values in cells D2 and D3. Now, we can enter this formula to find values that fall between this range:

COUNTIFS(A1:A15,'>='&D2,A1:A15,'<='&D3)

Now, you can easily change the values in those cells and our count will get updated. For example, we changed the range to 10 and 40:

I use Excel all the time to help me get things done quickly. It includes creating data and copying it to R, or creating a complicated SQL logic. Watch this short GIF to see how to create a CASE WHEN statement:

If you want to divide many cells by a constant number, type in that constant in a cell. Copy that cell and select the range of cells you want to divide, right-click and hit Paste Special, and select Divide. As you can see in the Paste Special options, you can multiply, add, or subtract. But I find divide and multiply option helpful to convert numbers to percentages and vice versa.

In the Paste Special options, you will notice a Transpose option. This option will make your data vertical or horizontal. See the GIF below:

Sale Excel 2019 Bible

• Alexander, Michael (Author)
• English (Publication Language)
• 1120 Pages - 10/23/2018 (Publication Date) - Wiley (Publisher)

Sale Excel 2016 Bible

• Wiley
• Walkenbach, John (Author)
• English (Publication Language)
• 1152 Pages - 10/26/2015 (Publication Date) - Wiley (Publisher)

Sale Microsoft Excel Data Analysis and Business Modeling

• Microsoft Press
• Winston, Wayne (Author)
• English (Publication Language)
• 864 Pages - 12/09/2016 (Publication Date) - Microsoft Press (Publisher)

Sale Microsoft Excel 2016 Step by Step

• Microsoft Press
• Frye, Curtis (Author)
• English (Publication Language)
• 544 Pages - 10/08/2015 (Publication Date) - Microsoft Press (Publisher)

Sale Excel 2016 Power Programming with VBA (Mr. Spreadsheet's Bookshelf)

• Wiley
• Alexander, Michael (Author)
• English (Publication Language)
• 768 Pages - 02/08/2016 (Publication Date) - John Wiley & Sons (Publisher)

Sale Excel 2013 Power Programming with VBA

• John Wiley & Sons
• Walkenbach, John (Author)
• English (Publication Language)
• 1104 Pages - 04/01/2013 (Publication Date) - Wiley (Publisher)

R for Excel Users: An Introduction to R for Excel Analysts

• Taveras, John L (Author)
• English (Publication Language)
• 212 Pages - 08/08/2016 (Publication Date) - CreateSpace Independent Publishing Platform (Publisher)

Last update on 2021-01-21 / Affiliate links / Images from Amazon Product Advertising API

Excel is one of the most perplexing pieces of software in the Microsoft Office suite. And is the most widely used spreadsheet software.

So, if you are the one who needs to regularly use this scary program then this article is going to be very helpful for you.

Whether you are just starting your Excel journey or have been using it for a while, there are few skills that will help you succeed in this complex program,

…and the best things about the Excel skills are this help to avoid the certain hazard and impress the other person with your work (especially your Boss).

Here learn about the 21 top Excel skills to make yourself more dynamic and an experienced user.

Highlights Of Advanced Excel Skills Checklist:

Choose the Right Layout	Use Fill Function
Use Named Ranges	Formatting
Converting to PDF	Protect Your Work
Control Data Input	Use Keyboard Shortcuts
Use PivotTable Tool	Customize the Ribbon
Create Charts	Use Custom Views
Grouping and Outlining	Automate Complex Tasks with Macros
Automating Sub-totals	Import Data from a Website

1) Choose the Right Layout

This is the very first thing that you need to include in your regular Excel work. Many Excel users have seen that just open Excel and start typing and hope for the best.

But this is not done…

You need to do a little planning before starting creating a spreadsheet. Understand your data, and given little time what you actually want to get out of it.

And try to layout your spreadsheet in a way to easily achieve what you want.

This little effort will make your work a lot easier and accurate.

2) Use Fill Function

The Fill function is a GREAT Excel skill that will make your work a lot easier and less time-consuming.

Suppose you need to create 12 months complex report in one workbook. And for this, you need to create 12 sheets from Jan-Dec. You laid out the Jan sheet and proceed to copy & paste the spreadsheet to the other 11 sheets.

Well, this is the very tiresome process and a small mistake while pasting data can become a big Blunder.

So, in this case, make use of the Excel’s “Fill Across Sheets” tool. This will allow you to duplicate one sheet across many others quickly and more precisely.

Additional Reading:

3) Use Named Ranges

This is the most advanced Excel skill that every Excel used to use while creating data.

Named ranges (definite names) are fundamentally absolute, and here you don’t require to put the symbols on anchor refs. In fact, use names in formulas, or rapidly select a named range.

The named ranges when applied to a single cell or a range of cells and you can see in the formula, it can be used as a direct replacement for the cell or range address.

This is by default set, and when you create a named range on one sheet, you can use that name on other sheet and always back to its original location.

4) Formatting

This is also very effective Excel skill that you need to implement in your regular Excel work.

Once you have created your spreadsheet – you will commonly do two things:

• Enter data or review data.

Neither of these things helped by a creation of fonts, sizes and colours, so to make your spreadsheet easy to understand, picks one font and stick with it.

Make use of the Bold or Italics, for highlighting the differences between headers and data and utilize light cell colour to pick out summary rows and formulas

And leave all the formatting till last one.

5) Protecting Your Work

While creating a perfect Excel report, one has to go through a great deal of trouble, so protecting the Excel file is highly important.

Excel is having great tools to protect your work and preventing others from doing any change.

You can protect formula cells to stop any other changes, stop the rows and columns from being resized and also stop the user from changing the structure of the workbook.

Well, at a highly advanced level, you can even allow certain users to edit the particular regions.

6) Converting to PDF

After creating your report, you need to send that Excel spreadsheet or report to the clients. Well, till here it is Fine, but do you really want them to look at your data and formulas. I think possibly NOPE.

If you answer is NO, then the simplest thing is converting your Excel report to PDF.

Though there are other numbers of ways, that will stop other to take a look at your data or formulas or change things on your spreadsheet such as (hide things, protect things or disguise values with formatting). But all these steps can be circumvented.

So here the best thing is simply to convert your spreadsheet to PDF and send it out without any worry.

Well converting Excel to PDF is easy and the entire current versions of Excel can do it without any additional software.

7) Use Keyboard Shortcuts

Keyboard shortcuts are very helpful to make your work easy.

Many users reach for the mouse to perform an action in Excel, but using the keyboard is not only easy but also speed up your work and saves time.

Here in this video watch the Excel keyboard shortcuts:

8) Control Data Input

For me and I guess every Excel user the most irritating thing is – incorrect data in Excel.

And in this Dates are one of the biggest culprits. Many users enter dates in a wrong way and it looks like just a text.

This not only makes you entire report down the line but also end up causing formulas to go wrong and filters not to work.

So, the solution makes use of the Data Validation, using this not only control the information types user enters into cells like numbers, dates or text but also the data range.

Also Read:Stuck With MS Excel Data Validation Error? Here Is The Quick Fix To Get You Going!

9) Use PivotTable Tool

This Excel skill is very time-saving; make use of the PivotTable Tool. This will help you to save yourself from large amount to analysis time.

This tool is available in the Excel itself, just go to “Insert” menu and look for the “PivotTable” icon.

The best way to use this is to click the icon after highlighting entire data table and the headers too. The PivotTable tool analyzes data and brings it into a new sheet and there you can manipulate the data as you like.

The PivotTable will automatically do the work, and save time to study the results.

10) Customize the Ribbon

Ribbons are very useful but quite frustrating when you need to switch from one tab to the other to find the commands that you want to use.

However, in Excel 2010, it is easy to customize the ribbon. And here you can add your own custom tabs and drop onto it a selection of the commands that are more frequently used and can even include macros.

11) Create Charts

Creating charts make your presentation, report easy to understand and as well impress your Boss.

Well, using the graphs and charts can be bit tricky, only if you want them. However, a basic chart can be produced in a couple of clicks.

The latest Excel version makes it very easy to try different charts and formats easily.

Also Read: How To Use Dynamic Data Labels To Create Interactive Excel Charts

12) Use Custom Views

A custom view is useful when you need to print your report. This records the entire current print settings and allows you to re-apply them easily at a later stage.

And in the print settings, it can record filters and hidden rows and columns also.

This is very helpful to avoid the issues while printing like page breaks, scale it correctly, headers and footers and others.

13) Grouping and Outlining

If you are having a lot of rows and columns on your spreadsheet, then it becomes a responsibility constantly scrolling left and right or up and down to get to the point where you want to be, just to go back where you started.

In this case, grouping and outlining will help you with this and allow you to quickly expand and collapse a number of rows or column. This is just like the super hide/unhide features and as you understand it, it is fairly easy to apply.

14) Automate Complex Tasks with Macros

Macros are highly useful and great tool available in Excel. With the help of macros, you can make Excel do the thing that it can’t-do previously. You can also complete the tasks in the couple of seconds that take an hour to complete.

Well, making use of the Macros can be complex but once you use it. This becomes a lot easy for you to complete the tasks with a blink of the eye.

15) Automating Sub-totals

While working on Excel one of the most important tasks is adding subtotals to a column of values.

For Example: If you have a sheet full of sales data and want to show how much of each product was sold. Then, in this case, you need to insert new rows after a group of product and then sum up the values above and repeat the process for every product.

I have seen many people who spend hours in doing that, which is highly frustrating. So, in this case, the sub-totals tool is highly helpful in doing the same job in a couple of seconds and as you are finished, this removes the totals rapidly, leaving the spreadsheet same as it was.

16) Import Data from a Website

Well, this is the last Excel skills that are very helpful and greatly speed up the workflow.

Learning how to import data from a website is very important. As if you see a website with the tone of useful data than just convert it into a worksheet.

• Just click File > Import External Data > click New Web Query.
• When you click this tab a new window open that displays browser homepage with the URL of the page.
• Select the web page that wants to display and copy paste the link into the Address box.
• Lastly, click OK and now your data is imported into an Excel file.

Well, these are some of the top, advanced and as well as essential Excel skill that you need to implement in your regular work to become more productive and save time.

What Excel Skills Are Employers Looking For?

Here is the list of Microsoft Excel skills which employers usually look for in the candidates they are hiring.

Case 1: Auditor Excel Skills

Check out the Excel Skills highly preferred by Employers in candidates applying for the post of auditor:

• Worksheet
• Charts
• VLOOKUP, HLOOKUP
• Functions
• Balance Sheet Audit
• Conditional formatting
• Pivot Tables
• Internal Auditing
• Internal Audit of Inventories
• Macros
• Operators

Case 2: For Senior Level / Excel Specialists / Excel Experts

Following are the Excel skill checklist for candidates applying for the post of Senior Level / Excel Specialists / Excel Experts:

Advanced Excel Techniques Pdf Tutorial

• Advanced Formulae
• Advanced Charting
• PIVOT Tables & PIVOT Reporting
• Conditional Formatting
• Tool Bars
• VBA & Macros
• Excel Worksheets
• Functions
• Data Tables, Simulations & solver
• VLOOKUP
• INDEX + MATCH

Case 3: For Entry Level / Administrative Job Roles

Below here is what advanced Excel skills you needs to have for Entry Level / Administrative Job Roles:

• COUNTIF / COUNTIFS
• SUMIF/SUMIFS
• Cell Formatting
• Data validation
• Data Filters
• Excel shortcut keys
• Data Sorting
• Managing Page Layout
• Charts & its Analysis
• Pivot Tables

Case 4: Business Analyst Excel Skills

Advanced Excel Training Free

Following are list of advanced Excel skills which employers look while hiring candidate for Business Analyst post.

• SUMPRODUCT
• Charts & Chart Analysis
• Conditional formatting
• IFERROR
• VLOOKUP
• MACROS
• Regression
• MATC
• INDIRECT
• Data Validation
• SUMIFS, COUNTIFS
• Merge Data
• IF Function
• Data Analysis
• Pivot Tables
• Histograms

Case 5: Data Analyst Excel Skills:

Have a look over the top Excel skills you must know if you are applying for Data Analyst post:

• Functions for data cleaning and manipulation: LEN, Remove Duplicates, Filter & Sort, Concatenation, Logic Functions, Conditional Formatting, IfError, Index Match, Find & Replace, Short-Cuts
• Pivot Tables
• LOOKUP Functions
• Macros
• Flat data tables
• Cross Reference Table
• Graphics Data
• Advanced Charts
• Data filters
• Operators
• Number Series
• Data Interpretation

Wrapping Things Up:

I tried my best to discuss some of the essential Excel skills.

Now it’s your turn to make use of the given advanced Excel skills and make your life easier and as well impress everyone with your work.

And also remember, there is always something new to learn about this powerful Microsoft office program.

So, whatever you do try to make your Best.

And also if you have any query, other MS Excel skills that I haven’t mention in this article than do let me know, you can visit our comment section box below.

That’s it…

Margret

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.