Advanced excel tutorial free download

Real learning happens when you attempt to solve problems on your own. Answers are provided, but do ask any questions if you get stuck. Your questions will always be answered.

Engaging — I have made every attempt to keep the course interesting and engaging by mixing talking head explanations with screencast demos. This allows you to easily pick what you want to learn now or review later. My main goal in this training is to increase your awareness of the top tools and formulas in Excel to help you solve complex problems.

Becoming advanced in Excel takes practice, readiness to learn new methods and willingness to apply these to practical cases. Check DisciplineEvent column in the Events table. Step Once again, click on Diagram View.

A line appears between the Events Table and the Medals Table, indicating a relationship has been established. The line and the fields defining the relationship between the two tables are highlighted as shown in the image given below. To do so, a field with values that uniquely identify each row in the Hosts table is to be found first.

Then, search the Data Model to see if that same data exists in another table. This can be done in Data View. There are two ways of doing this. The Data View appears. Step 2: Click on the Hosts table. Step 3: Check the data in Hosts Table to see if there is a field with unique values. There is no such field in Hosts Table. You cannot edit or delete existing data using PowerPivot. However, you can create new columns by using calculated fields based on the existing data.

Adjacent to the existing columns is an empty column titled Add Column. PowerPivot provides that column as a placeholder. The Add Column is filled with values. Check the values to verify that they are unique across the rows. Step 5: The newly created column with created values is named CreatedColumn1.

To change the name of the column, select the column, right-click on it. Step 6: Click on the option Rename Column. Step 8: Now, Select the Medals Table. Step 9: Select Add Column. Step Rename the Column as Year. Step Select Add Column. Step Rename the column as EditionID. Step Sort the Column in Ascending Order. Relationship using calculated columns Step 1: Switch to Diagram View. Ensure that the tables Medals and Hosts are close to each other.

PowerPivot creates a relationship between the two tables. A line between the two tables, indicates the relationship. Excel — External Data Connection Advanced Excel Once you connect your Excel workbook to an external data source, such as a SQL Server database, Access database or another Excel workbook, you can keep the data in your workbook up to date by "refreshing" the link to its source.

Each time you refresh the connection, you see the most recent data, including anything that is new or has been deleted. Let us see how to refresh PowerPivot data. Step 1: Switch to the Data View. Step 2: Click on Refresh. Step 3: Click on Refresh All. After the refresh is complete, the status is displayed. Step 4: Click on Close.

The data in your Data Model is updated. Update the Data Connections Step 1: Click any cell in the table that contains the link to the imported data file. Step 2: Click on the Data tab. Step 3: Click on Refresh All in Connections group. Step 4: In the drop-down list, click on Refresh All.

All the data connections in the Workbook will be updated. Step 1: Click any cell in the table that contains the link to the imported Data file. Step 3: Click on Connections in the Connections group. The Workbook Connections window appears. Step 4: Click on Properties. The Connection Properties Window appears. Click on the Usage tab. The options for Refresh Control appear. Step 6: Select Refresh data while opening the file.

You can use this option to save the workbook with the query definition but without the external data. Step 7: Click OK. Whenever you open your Workbook, the up-to-date data will be loaded into your Workbook. Step 3: Click on the Connections option in Connections group. A Workbook Connections window appears. A Connection Properties Window appears. Step 5: Click on the Usage tab. Your data will be refreshed every 60 minute that is every hour. Enable Background Refresh For very large data sets, consider running a background refresh.

This returns the control of Excel to you instead of making you wait several minutes for the refresh to finish. You can use this option when you are running a query in the background. However, you cannot run a query for any connection type that retrieves data for the Data Model. Connection Properties Window appears. The Refresh Control options appear. For example, you can expand the source data to include more rows of data.

However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable. Step 1: Click anywhere in the PivotTable.

Step 4: Click on Change Data Source. The current Data Source is highlighted. If you want to base your PivotTable on a different external source, it might be best to create a new PivotTable. If the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current connection.

Step 4: Click on the option Choose Connection. In the Show box, keep All Connections selected. All the Connections in your Workbook will be displayed. Step 6: Click on New Source. Go through the Data Connection Wizard Steps. Step 3: Click on Select in the Actions Group as shown in the image given below. The entire PivotTable will be selected. Step 5: Press the Delete Key. To do this, follow the steps given below. Step 1: Right-click on the Worksheet tab.

Step 2: Click on Delete. You get a warning message, saying that you cannot Undo Delete and might lose some data. Since, you are deleting only the PivotTable Sheet you can delete the worksheet. Step 3: Click on Delete. Using the Timeline A PivotTable Timeline is a box that you can add to your PivotTable that lets you filter by time, and zoom in on the period you want.

This is a better option compared to playing around with the filters to show the dates. It is like a slicer you create to filter data, and once you create it, you can keep it with your PivotTable. This makes it possible for you to change the time period dynamically. Step 3: Click on Insert Timeline in the Filter group. An Insert Timelines Dialog Box appears. The timeline for your PivotTable is in place. Step 1: Click the small arrow next to the time level-Months.

The four time levels will be displayed. The Timeline filter changes to Quarters. Step 3: Click on Q1 The Timespan Control is highlighted. The PivotTable Data is filtered to Q1 Step 4: Drag the Timespan handle to include Q2 The timeline is cleared as shown in the image given below. You can even create a PivotChart that is recommended for your data. Excel will then create a coupled PivotTable automatically. Step 1: Click anywhere on the Data Table.

The Insert Chart Window appears. Step 4: Click on the Recommended Charts tab. The charts with the PivotChart icon in the top corner are PivotCharts.

A Preview appears on the Right side. Your standalone PivotChart for your Data is available to you. Excel — Power View Advanced Excel Power View is a feature of Microsoft Excel that enables interactive data exploration, visualization, and presentation encouraging intuitive ad-hoc reporting.

Step 1: Click on the File menu and then Click on Options. The Excel Options window appears. Step 2: Click on Add-Ins. Step 3: In the Manage box, click the drop-down arrow and select Excel Add-ins. Step 4: All the available Add-ins will be displayed. Now, you are ready to create the Power View sheet.

Step 1: Click on the Data Table. Step 2: Click on Insert tab. Under the Power View Fields you will find Areas. Excel — Visualizations Advanced Excel You can quickly create a number of different data visualizations that suit your data using Power View.

Create Charts and other Visualizations For every visualization you want to create, you start on a Power View sheet by creating a table, which you then easily convert to other visualizations, to find one that best illustrates your Data.

Step 1: Under the Power View Fields, select the fields you want to visualize. Step 2: By default, the Table View will be displayed. As you move across the Table, on the top-right corner, you find two symbols — Filters and Pop out. Step 3: Click on the Filters symbol. The filters will be displayed on the right side. Filters has two tabs. View tab to filter all visualizations in this View and Table tab to filter the specific values in this table only. Step 3: Click on Matrix.

The Matrix Visualization appears. Step 3: Click on Card. The Card Visualization appears. You can use several design options in a chart such as showing and hiding labels, legends, and titles. Charts are interactive. The charts are interactive in a presentation setting also. Step 1: Create a Table Visualization from Medals data. You can use Line, Bar and Column Charts for comparing data points in one or more data series.

In these Charts, the x-axis displays one field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the Chart. Line Charts distribute category data evenly along a horizontal category axis, and all numerical value data along a vertical value axis. Step 3: Create the same Table Visualization below.

Step 6: Click on Line. Step 7: Click on the Line Chart Visualization. Step 9: Click on the Stacked Bar option. That portion of the bar is highlighted. Only the row containing the Data specific to the selected bar is displayed in the table above. You can use the column charts for showing data changes over a period of time or for illustrating comparison among different items. In a Column Chart, the categories are along the horizontal axis and values are along the vertical axis.

Step Click on Stacked Column. Step 1: Click on the Table Visualization as shown below. Step 3: Click on Pie as shown in the image given below. You can also make your Pie Chart Visualization sophisticated by adding more features.

Step 1: Add Field Gender to the Table above. Step 2: Click on Pie Chart Visualization. In Scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart. In a Bubble Chart, a third numeric field controls the size of the data points. Step 3: Click on Scatter.

The Data points are little circles and all are of same size and same color. The data points are circles of the size represented by the values of Data points. The data labels are the Category Values.

You can pause at any point to study the data in more detail. All the bubbles of that color will be highlighted and other bubbles will be grayed out. Maps You can use Maps to display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map.

To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding. So, it asks you to enable the content. Adding locations and fields places dots on the map. The larger the value, the bigger the dot. When you add a multi- value series, you get pie charts on the map, with the size of the pie chart showing the size of the total. Step 2: Drag a numeric field such as Count to the table. Step 4: Click on Map in the Switch Visualization group.

The Table Visualization converts into Map Visualization. Power View creates a map with a dot for every geographic location. The size of the dot is the value of the corresponding numeric field.

Step 5: Click on a dot. The data, viz. The Dots are converted into Pie Charts. Each Color in the Pie representing the category of the Medals. The Dot gets highlighted and zoomed. The details of the Pie Slice are displayed. Step 9: Place the cursor on one of the Dots and click on it. That Pie Slice is highlighted. The other Slices in the Pie and all other Pie Dots will gray out. You can have Multiples arranged side by side, making it easy to compare many different values at the same time.

Multiples are also called Trellis Charts. Step 1: Start with a Pie Chart. Click on the Pie Chart. Step 2: Drag a Field to Vertical Multiples. Step 5: Click on Grid Width and select a number.

Vertical Multiples expand across the available page width and then wrap down the page into the space available. If all the multiples do not fit in the available space, you get a vertical scroll bar. The horizontal multiples expand across the page.

If all the multiples do not fit in the page width, you get a horizontal scroll bar. Step 7: Click on Multiples. You have created Horizontal Multiples of the Line charts. Visualization — Tiles Tiles are containers with a dynamic navigation strip.

You can convert a Table, Matrix or Chart to Tiles to present data interactively. Tiles filter the content inside the Tile to the value selected in the Tab Strip. You can have a single Tile for each possible field value so that if you click on that Tile, data specific to that Field is displayed. All the content in the container is filtered by the selected Tile value. The Tile container has two navigation strip types: tile flow and tab strip.

What you have created above is the tab strip. Tab strip displays the navigation strip across the top of the visualization. Step 3: Click on a Tile. Step 5: Click on Tile Type in the Tiles group. Step 6: Click on Tile Flow. The selected Tile is always centered. When you Scroll, the Tiles go on being selected. Step 7: Click on Map in the Switch Visualization group. Step 8: Drag Medal to Color. Step 9: De-select the Field Gender. You got Map Visualization with Tile Flow.

Likewise, you can have any data visualization with Tiles. Power View — Additional Features Advanced Excel Power View in Excel provides an interactive data exploration, visualization, and presentation experience for all skill levels as you have seen in the previous section. You can pull your data together in Tables, Matrices, Maps, and a variety of Charts in an Interactive View that brings your Data to life.

New features have been added to Power View in Excel Power View sheets can connect to different data models in one workbook. You can copy and paste a chart or other visualization from one sheet to another, but only if both sheets are based on the same Data Model.

If you base your Power View sheet on the internal Data Model, you can make some changes to the Data Model while you are in the Power View sheet itself. Step 1: Select the worksheet Salesperson. Step 2: Now select the Worksheet Sales.

You have a Range of Data of Sales. Step 3: Convert the data in the worksheet Salesperson to table and name it Salesperson. I am an Excel trainer who shares tutorials and videos online.

You can reach out to me at sumitbansal trumpexcel. Hello Kimberly… Yes, the course is free. Just click on the video and watch. Is there any free online course work that can be done by the person on hands learning to be learning by doing? Many thanks. Just wanted to say—Sumit, Big thank you for taking time for providing these free online training courses. God bless you. This is the best course on the internet!!

I need to develop a tactical crime analysis matrix using a spreadsheet. The matrix will contain 10 records rows and 16 variables columns.

I have a list of crimes. Would you please demonstrate a sample of this. Congratulations Sumit Bansal for this exceptional work!

It is really fantastic to have access at this great content. There are so many things you can do by just using excel properly! Do you mind if I make a post regarding your site on LinkedIn or on twitter? Hi Pooja… There was some minor update going on which prevented the videos from loading. You can watch these now. There is no restriction. I will jump to the third and hope the 2nd returns. Hello Morgan, There was some minor update going on which prevented the videos from loading.

You can watch all the videos now. I have really found you to be clear and concise in your presentation. It is very easy to follow and is a great brush up for my rusty skills. Thanks you!!! There used to be a feature in excel that let you send the sheet to a list of people to add data in a sequence — is this still available? We glad to have this guide for our reference to learn the Excel free.

Thank you in advance… I am working in excel from last 7 years, wanted to learn more 1 passionate about excel 2 need a better career growth, let me start with this, if require will take help further. I have a small problem with vlookup i am getting few errors on few cell and i am unable to solve the issue can u pls help me. Hello I want to create a business cards alphabetical file Is there any tips you can give me to make it easier. Just click on a video and start watching.

I feel you are doing charity on the behalf of excel. Sir I salute you. God may bless you through your ahead intiatives. Thank you, sumit bansal.

Hello sir, thankyou so much for free excel online training materials. This will gonna really helpful to people who want to learn excel online for free.

I would like to know is there any Certificate available after compilation of this course? I Have Data in sheet. You can get it from pivot table, select d whole data, go to insert top left side you will get pivot table option. I promise to recommend it to friends too. Thanks a lot for this free excel training course.

Never thought that i can learn so many things in this course. My confidence level has gone up now. I was looking for a way to learn Excel for free. I just lost my job and I see that most of the companies require knowledge of using Excel. I will let you know my results when I am done here!

Nothing to do enroll. All things are in front of your finger tips, start with praying to sumit bansal and keep going, rock on. Thanks Sumit for your excellentwell-paced method of teaching the basics in excel. I was just asking my son for some assistance when I found your video.

This has given me hope that I may be able to pursue your advanced Excel course if I master the basics. O I am so excited! Really appreciable sir, you are extremely brilliant, and really doing good work for helping student in excel tutorial, each and everything you are making clear to student in each video even tiny things you are covering and your voice is so clear and very simple way to make understand.

Thanku sir for this wonderful excel videos but sir i want certificate with this so can you provide certificate of this or not? For certificate you can go through udemy! Course,, if you are willing to get certificate, thank you, rock on!

This is the most helpful teaching website regarding excel I have come across online. Your teaching style is really passionate, your way of explanation is very clear and you obviously deserve a load of respect and appreciation.

It helps me a lot in better understanding excel, and also motivates me to dive deeper into further learning. Thanks very much for tutorials, I like to know if there is a way to make just a cell behave as a normal calculator. This other cell will be the key-in cell or active cell. Kindly share a video if this is possible. I have searched online and came across your youtube video tutorial on how to count color using function and macro module programing in excel.

I am traying to count the highlighted cell that contains a specific numbers inside of the cell highlighted in color using your code. What should I change to add the values inside of the cell instead of 1?

I would like to learn the importance of this course which helps me in resume as an addon and can be fast in computer operations with Latest knowledge. Yes, Sumit it is exciting to Join with your Free Excell training videos. I learnt a lot and appreciate your Teaching skills. It is so easy to understand each step as you move forward. Thanks and Blessings. Great work. Hello I am reviewing your free excel training course.

Does your course offer practice after watching the videos? I am trying to give this to my employees. However, I would like to give them a practical test to be sure they went through all training modules.

Could you help with this? I tried to clean data by using the methods suggested by you in one of your lesson. But could not successfully clean certain data sets. How can I send it to you for your help? I receive more knowledge related Excel thinks you if you have other levees of excel send me linked.

I feel very happy not only to be enriched with learning Excel tips by you but also to be enjoyed by your way of imparting training. How can I be able to download the book? Please suggest me. Excellent Sumit! I truly wish to appreciate your way of teaching and effort you put in while creating these videos. I learnt a lot. I have a folder in which I have approx photos and want to search old photos of same person i clicked today. Hi, Im subscribed to a mail list for 31 videos [Excel Video 8 of 31] today buy i want to subscribe a friend but I dont see where he has to click to subscribe to the list.

Can you tell me what he neds to do? Thank you Sumit for giving us your time and teaching us Excel. I am subscribed to your YouTube channel. The Excel in two minutes series contains nine parts.

I am unable to access parts five through nine because they are listed as private. If this is in Error will you correct it so I can view those videos please? Can you try again Rose?

Can you help me with this Any suggestions? Hello Courtney.. You can find the videos as soon as you click on the gray bar.

Good morning. I am having the problem where no video pops up when gray bar is clicked. Any suggestions? Hi I have hundreds of rows and I want to multiple select rows over 10 columns with bold fonts only, I saw you video on filtering cells that contain bold font, do you have similar for rows with bold font instead of cells? Hi… your videos are very detailed and very helpful…thank you for all your efforts in sharing your knowledge..

Just seen 4 of them today intial ones and its superb, no need to go anywhere. Simply and Easiest way to learn excel from Sumit Bansal. I want to know If i want to create a data for my students in which i have name ,address,phone and email And categories them using Drop down feature according to their subject.

The only problem that I am having is that some of them work and others do not. Is there an easy fix for this? As a beginner with Excel, I really enjoy your tutorials and it helps a lot…… thank you Mr Simit, Hope to learn more from you as I go along with more free tutorials. Truly appreciate the time and effort you put in to this for free!

Maybe add a donate button or something so people that want to can show their appreciation with moolah! Today is Thursday, August 8, and I used these videos to relearn how to use excel. They were extremely helpful. Even more happy this training was free. You are a miracle. Created vids, summaries for free. More than just an average youtuber.

Thank you so much. Greetings from Long Beach CA. Thank you so much for this course it is really helping me you are such a good person to do this for us all. Now I have a question, not about your course, but if you coluld solve this problem I would be grateful.

Do you know how to get rid of the usernaume in comments? I think it must be solved by VBA code? I think it is very annoying that I have to delete my name or initials in everey new comment I make.

Must be a way to get around it? Dear Sumit Bansal, I am really appreciate you concern to teach people without any interest. This course will make you an intermediate Excel user. I will write credit to your name and mention your website any other services you offer. Thank you so much Sumit! Great job! One question, I am calculation the available FTE per month with Excel Leave Tracker Template, I have to use the of work days, how I am going to calculate this value and use it in the summary table.

Thank you so much for this structured and comprehensive Excel Tutorials which I found very informative, and the best part of it is that it is totally free. I really appreciate your time and effort giving an opportunity for those who need this materials, including myself.

I believed this free Excel Tutorials will help me a lot to land my home-based job. Hello Sumit, I like how you have divided the course into different modules and videos. I have done accounting and finance in the past so I feel this is really needed for my growth and career. If I complete all these videos and practice, will I be able to operate basic to intermediate excel? Hello Shriya.. Glad you found the course useful. Yes, you would be able to handle basic to intermediate tasks in Excel after watching the videos and going through the practice files.

Thank you Sumit. Also wanted to ask you, I use Macbook so there are few things which are different in Excel in Macbook. If possible can you upload a video where you mention those few differences.

For eg. Can you help me with that. You are a fantastic man to offer your services to educate those that need to learn about excel …. How refreshing it is to hear you speak not too fast, nor too slow in a manner that is clear and understanding. Thank you most kindly. Working alright at my end. Kindly help me how can I access these options? I guess I need to upgrade excel with version. Is this a certification course? If not, then what should we do to get a certificate for our learnings?

I created a link to enable the contents of Sheet 1 Cell A1 to appear automatically in Sheet 2 so that whenever I change the contents of Sheet 1, the same automatically changes in Sheet 2. Question: How do I change the settings of my laptop at home to enable me jump from Sheet 2 to Sheet 1 upon double-clicking the link in Sheet 2, Cell A1, to enable me to go to the actual location of my data in Sheet 1?

Hi Sir! Thank you for this three tutorial. What should I do? When you click on the gray bars on the page above, you will get the video that you can watch. Are you not getting any videos when you click on the gray bar? Can you please try now? Also, refresh the page hold the control key and then hit the F5 key. This should fix the issue. Click on the link and then scroll down slowly, so you do not pass it up, until you see the box with the video area…. Sumit You are truly an MVP.

Your videos and instructions are fantastic, they are easy to understand, not cumbersome or a burden to watch and your instruction is simple and precise. I have used Excel for a very long time but each time I need an answer you are my go to search and instructor and it goes without saying I always find what I am looking for. Are you as good in other aspects of the MS Suite or Master of one?

Let me know. Thanks for your support. Paramveer Sidhu. Your website provides excellent insight in Excel which is very useful for every type of end user. Need your support on power query. We are truly devoted to our students to groom their knowledge in Excel and VBA and help them to reach on the top of the ladder. Advanced Excel Institute is the excel tutorial where you can find the solutions of all your Excel and VBA related problems.

Our all the sessions are based on practical approach with all the practical examples and data. We have highly experience Corporate Trainer Mr. All the sessions will be on live streaming mode not the pre-recorded sessions.

Kindly contact us for more details. For me Excel is a new learning every day and pairing my knowledge with others. This is what I have always been very passionate about and that's what I am doing very religiously. To get detailed information about the course and demo session details, kindly fill your details or contact us.

If you have interested any of above course which we provide, So send us a mail we will reply you soon!! Excel Corporate Training.