Advanced Excel Full Course 2023 | Excel Tutorial For Beginners | Excel Training

Introduction to Microsoft Excel In today's world, data is a critical component of decision making. From mobile ads to stock investments, data-driven decision making is essential. To handle and analyze data, the IT industry demands professionals with specific skills, including Microsoft Excel. Simply Learn has curated a full course on Microsoft Excel in collaboration with industry experts to help professionals match the current industry standards. What is Microsoft Excel? Microsoft Excel is a software tool developed by Microsoft to store data in an organized way with rows and columns. It allows users to manipulate data through mathematical operations and extract insights from data, represented in visually appealing graphs and charts. Fundamentals of Microsoft Excel Before getting started with Microsoft Excel, it is crucial to understand its fundamentals. The Microsoft Excel homepage shows various sheets and provides suggestions based on the type of sheet you want to work with. The toolbar menu, including options such as file, home, insert, raw, paste, layout, formulas, data, review, view, and help, is used to work on data. The toolbar ribbon, segmented into groups, has various options to perform different operations. Each group has a separate function, and the toolbar more options are used when the group is not able to fit all functionalities in one section. Demo Inventory in Microsoft Excel The practical demo of creating an employee sheet in Microsoft Excel is illustrated. The home page of Microsoft Excel has a blank workbook. You can create a new workbook by selecting various types of sheets based on your requirement. The toolbar ribbon belongs to the home page, and different tools have different ribbons and groups. The cells in Microsoft Excel are named and have their own address, and the sheet tracker in the bottom left corner helps navigate through different sheets. Overall, Microsoft Excel is a powerful tool that is widely used for data analysis and decision making in the IT industry. Simply Learn's full course on Microsoft Excel covers all the necessary skills required to handle and analyze data to make data-driven decisions. Fundamentals of Microsoft Excel Before getting started with Microsoft Excel, it is important to understand the basics. Cells in every sheet have an address, consisting of a column name and a row name. Multiple sheets can be created by selecting the plus option, and navigation can be done by pressing on the sheet name. Cell and sheet sizes can be adjusted by pressing on the bottom right corner of the sheet. Employee Details in a Company A table for employee details can be created by adding columns for name, employee number, designation, salary, blood group, and phone number. Rows can be added by right-clicking and selecting the insert option. The font and size of the table can be changed, and the cells can be aligned to the center. The text can also be converted to lowercase or uppercase using Excel formulas. Adding Rows to Existing Data If a row needs to be added to existing data, right-click on the row and select the insert option. To add multiple rows, select multiple rows and insert them. Adding Rows and Columns in Excel To add a row in Excel, right-click on the row number and select "Insert". To add a column, select the column next to where you want to add it, right-click, and select "Insert". To add multiple columns, select the columns you want to add, right-click, and select "Insert". Formatting Data in Excel To format data in Excel, select the cells you want to format and choose the desired format from the "Data Type" options. To add a currency symbol, select the "Currency" option and choose the desired currency. Comparing Columns in Excel To compare columns in Excel, use the built-in "Conditional Formatting" feature. Select the data you want to compare, navigate to the "Conditional Formatting" options, and choose "Duplicate Values". You can also compare columns by directly using the "Equal to" function and dragging it down to compare all cells. Comparing cells in Excel can be done in various ways: Using Conditional Formatting and the Equals To operator with IF operator. Using Lookup functions such as VLOOKUP. Adding wildcard symbols to the VLOOKUP formula to account for slight text differences. To transpose rows and columns: Select all cells and press CTRL+C. Go to Paste Special and select Transpose. Alternatively, use the TRANSPOSE formula. To group and ungroup columns and rows: Select columns or rows and go to the Data tab, then select Group. To minimize or unminimize groups, click on the minus or plus icon. To ungroup, select the columns or rows and go to the Data tab, then select Ungroup. To delete blank rows: Use the Find and Select option and select Blanks. Press CTRL and the minus key to delete the cells. Using Excel Functions To count the number of cells in a row, use the COUNT function and specify the range of cells to be counted. To remove empty rows, apply filters to the table and select the rows with zero data. Freezing rows and columns can be done through the View menu and selecting Freeze Panes. Converting Numbers to Words Excel does not have a built-in function to convert numbers to words, but a macro can be created using VBA. Microsoft offers a pre-defined macro code that can be used for this purpose. To activate the Developer option, go to the Options menu and select Customize Ribbon. Check the Developer option and create a new macro to paste the code. The function can then be used in Excel to convert numbers to words. Combining Data in Excel To combine data from multiple cells into one cell, use the concatenate operator (&) and specify the range of cells to be combined. Use spaces and other characters to separate the data as needed. This can be applied to all cells in a column to combine data for multiple employees. Working with Sales Data in Excel To analyze sales data for a single employee in a given month, use Excel functions such as SUM, AVERAGE, and MAX to calculate the total sales, average sales, and highest sales for that month. This can be extended to analyze sales data for multiple employees or over a longer period of time. Merging and Centering Cells in Excel If you have sales data for multiple months and want to merge it into a single quarter, you can use Excel's merge and center feature. To do this, select the cells you want to merge, go to the home button, and click on the "merge and center" option in the alignment group. This will create a merged cell with the data centered. If you need to unmerge the cells, simply select the merged cell and click the "unmerge" option. You can also use the "merge across" option to merge all cells in a row at once. This can be useful for creating separate rows for reviews and comments. To add a date to your spreadsheet, you can use the shortcut "ctrl+;" to insert today's date, or "ctrl+shift+;" to insert the current time. If you need to format dates, select the data and go to the data toolbar, then select "text to columns" and choose the delimited icon. From there, you can select the date format you prefer. Adding Granularity to Data in Excel To add more granularity to your data, such as day and time, go to Format Cells and select "Custom". From there, you can add the desired date and time formats. Calculating Age in Excel To calculate age in Excel, use the DATEDIF or DATE DIFF function. Use the formula "=DATEDIF(date of birth, TODAY(), "y")" and replace "y" with "d" for days or "m" for months. Calculating Time Difference in Excel To calculate the time difference between two times in Excel, use the formula "=out time - in time" and format the cells as desired. Using DAX Functions in Excel DAX (Data Analysis Expressions) is a formula language used for relational data and dynamic aggregation functions in Excel. DAX functions include table-valued functions, filter functions, aggregation functions, time intelligence functions, and more. Calculating Retirement Age Using DAX Functions To calculate retirement age using DAX functions in Excel, use the formula "=DATEADD(sheet1[joining date], 65*12, "m")", where "sheet1[joining date]" is the joining date column and 65 is the retirement age. DAX Date and Time Functions in Excel One of the several DAX functions in Excel is the DAX Date and Time Functions. To demonstrate this, let's assume we have data on employee names, phone numbers, and retirement dates. We want to identify employees who did not provide phone numbers using a checkbox. To enable the Developer option, right-click anywhere on the ribbon, select Customize the Ribbon, check the Developer option, and press OK. Insert a checkbox and customize it as needed. You can copy it to all cells and check or uncheck as necessary. You can also include the checkbox in a formula. When the box is checked, it returns a true value, and when unchecked, false. To do this, right-click the cell, select Format Control, and select the Cell Link option. You can use this cell reference in your formula. Adding checkboxes in Google Sheets is much simpler. Just go to the Insert option and select the Checkbox option. Adding Excel Documents to PowerPoint To add an Excel document to PowerPoint, create an icon of the Excel file. Click on Insert in the toolbar, navigate to the Text group, and select Object. Choose Create from File and browse for the Excel file. Click on Display as Icon and choose an icon from the options provided. Adding Images to Excel Spreadsheets You can add images to Excel spreadsheets by clicking on Insert, selecting Illustrations, and clicking on Pictures. Select images from your device or online sources. You can adjust the size of the image using the buttons provided. You can also add graphs and charts to showcase your data dashboard. Hiding Rows and Columns in Excel You can hide rows and columns in Excel by selecting them, right-clicking, and selecting Hide. If you want to hide an image along with the row, select the image, go to Picture Format, and select Format. You can then hide the row, and the image will also be hidden. How to Insert PDF into Excel To insert a PDF into Excel, you can use the following methods: Select the cell where you want to add the PDF, go to Insert, navigate to Text option, click on Object, select Create from File, browse for the PDF, and insert it. You can also add a logo to the PDF by selecting Display as Icon button. If you want to add a dedicated logo or image to the PDF, go to Insert, select Illustrations, click on Picture, browse for the image, resize it to fit the cell, and link it to the existing file or web page. How to Convert PDF into Excel To convert PDF data into Excel, follow these steps: Open a blank workbook in Excel and go to Data, select Get Data, choose the From File option, select PDF, navigate to the file location, and import it. Select the table(s) you want to load and choose the Load option. The data will be loaded in a tabular format in a new Excel sheet. How to Add Tick Mark in Excel You can add tick marks and X marks in Excel using conditional formatting. Follow these steps: Click on the cell where you want to add the tick mark or X mark, go to Conditional Formatting, select Icon Sets, and choose the tick mark or X mark as per your preference. Set the values for the tick mark or X mark, and select the color you want to use for the cell.