How much time do you spend every day using Excel? If you’re like me, then I bet you don’t even realize how much time you waste in Excel. In this video, I share 11+ excel formulas, keyboard shortcut tricks and other useful tips to save you tons of time.
Excel is a powerful tool that allows you to create complex data analysis reports and spreadsheets. However, it takes a long time to master. That’s why I created this video series where I show you some of my favorite Excel shortcuts and formulas.
Top Excel Formulas
1. IF(AND( ), ,0) – returns 0 if criteria isn’t met
2. IFERROR(A2-B2,0) – returns the value from A2 if B2 doesn’t exist
3. SUMPRODUCT((WEEKDAY($E$3:$E$5)6)*(MONTH($F$3:$F$5)8)) – calculates total sales for weekdays in August
4. SUMPRODUCNT(–(H$9G9),–(I$9G9)) – counts all entries with same value in column H as G9
5. COUNTBLANK(D$10:D$13)-counts blank cells in range D10:D13
6. COUNTA(D$10:M$13)-counts number of nonblank cells in range D10 to M13
7. IFERROR(INDEX(C$17:Z$20,SMALL(ROW(C$17:C$20)-MIN(ROW(C17:Z17)),COLUMN(C$17:XFD))),””) – finds last row with value in column Z
8. IFERROR(LOOKUP(2^15,SEARCH(“a”,A1&”b”),{1;2}),””) – searches for “ab” pattern in cell A1
9. VLOOKUP(2,{1,2},OFFSET(A1,0,1)) – looks up numbers in array {1,2} based on lookup_value in cell A1
10. IF(ISNUMBER(SEARCH({“*”,”%”},A1)),SUBTOTAL(9,OFFSET(A1,-MAX(IFERROR(SEARCH(” “,A1),0)+1,1)),1),””) – checks if string contains any wildcard characters
11. IF(OR(ISNUMBER(FIND(“[“,A1)),ISNUMBER(FIDDLE(A1,1)))),SUBTOTAL(109,OFFSET(A2,0,1),1),””) – checks whether cell contains brackets or not
How To Use Excel Like A Pro | Tips And Tricks For Beginners
This is an introduction to macros in excel. We have covered workbooks, worksheets and used them to store our macro’s testing data. As well we’ve also looked at functions and their usage.
The excel skills that we are going to cover include:
• Creating a basic excel document
• Understanding sheets
• Using ranges and putting your values into those ranges
• Summing and getting averages
• Counting and identifying blanks
• Comparing two different sets of numbers
In this tutorial, we will cover
1. The basics of creating a new excel file
2. What a sheet is and what it can be used for
3. Working with Ranges
4. Functions
5. Summation and averaging
6. Counting and identifying blanks
7. Comparing two different sets
8. Working with Macros
9. Macro security
10. Debugging macros
11. Summary
Creating a New Excel File
To begin with, let’s start by opening a new excel file. This can be done through the menu bar or by right clicking on the desktop and selecting open. Once you have opened the excel application, you should see something similar to the following image below.
Now, let’s add some data to the spreadsheet so that we can test out our macro. Click on the Data tab and click Add. This will allow us to enter data into the spreadsheet.
Now, lets try entering some random data into the first few rows of the spreadsheet. Let’s say that we want to enter some data into the spreadsheet. So, we’ll type in some text into the first three columns of the spreadsheet. Then, we’ll press Enter. Notice that we now have added some data to the spreadsheet.
So far, we have only entered text into the spreadsheet but we haven’t actually put anything into any cells yet. So, let’s go ahead and change one of the cells. Lets say that we want to make sure that the second cell has a dollar sign in it. So, we’ll select the cell that we want to edit and then press F2. Now, notice how the cursor changes color from black to red. This means that we’re about to perform some editing action. So, when we press Enter, the cell will be changed to $100.
So far, we’ve just been adding some text to the spreadsheet. However, we can do more than just that. We can use formulas to manipulate the information in the spreadsheet. In the next section, we’ll look at using formulas to manipulate the data in the spreadsheet.
What Is A Sheet?
Sheet is a term used to describe a collection of cells within an Excel document. There are several types of sheets available including:
• Normal Sheets
• Chart Sheets
• List Sheets
• Worksheet Objects
• Pivot Tables
• Charts
• Custom Lists
Each sheet has its own purpose. Some sheets are used to display data while others are used to calculate data.
Let’s take a quick look at each of these types of sheets.
Normal Sheets
These are the most common type of sheets found in an Excel document. They are used to display data and they are typically named after the type of data that they contain. For example, if we wanted to create a list of all of the names of students who scored above 90% on their last exam, we could simply name the sheet “Students Above 90”. If we wanted to find out how many people were enrolled in a class, we would name the sheet “Class Enrollment” or even “Enrolled Students”.
Chart Sheets
These are also referred to as charts. They are used to graphically represent data. For example, if I wanted to show the number of students enrolled in my classes over time, I could create a chart called “Student Enrollment”.
List Sheets
This is another type of sheet that displays lists. It is usually used to track inventory numbers or other items that need to be tracked. For example, if a company wants to keep track of the amount of stock that they have for sale, they might create a list called “Stock Inventory”.
Worksheets Objects
This is a special type of worksheet object. These objects are used to store calculations or formulas. For example, if someone wanted to calculate the average GPA for all of his/her students, he/she could create a formula like this: AVERAGE(B1:C10) which would return the average GPA for all 10 students listed in column B.
Pivot Table Sheets
These are used to organize and analyze large amounts of data. They allow you to quickly view different aspects of your data. For example, suppose that you had an employee database with hundreds of employees. You could create a pivot table that shows how much money each employee makes per year. Then, you could use filters to see how much each employee earns based on gender, job title, etc.
Charts
These are used to graphically present data. For example, you could create a bar chart showing the total sales revenue for each month. Or you could create a line chart showing the number of customers who bought something during each quarter.
Custom Lists
These are used to store custom lists of data. For example, a person may want to keep track of all of the times that she takes her dog to the vet. In order to do so, she could create a list called “Dogs Visited Vet”, then add a row for every visit. This way, she can easily remember when she went to the vet and what was done.
What Are The Different Types Of Spreadsheets Used In Business?
Spreadsheets are used by businesses to help them manage information. They are commonly used to record financial transactions such as invoices, paychecks, expenses, etc. However, they can also be used to track inventory levels, customer orders, employee performance, etc.
There are two main types of spreadsheets:
Microsoft Excelspreadsheet application created by Google. It is very similar to Microsoft Excel but there are some differences between the two programs. Google Sheets is more user-friendly than Microsoft Excel. It is easier to navigate and it offers features such as drag-and-drop functionality and automatic formatting. Also, unlike Microsoft Excel, Google Sheets
Google Sheets
The first one is Microsoft Excel. It is a very popular spreadsheet program that has been around since 1990. It is available for both Windows and Mac computers. It allows users to make tables, graphs, and charts. It is widely used in business settings because it is easy to learn and use.
Conclusion
A spreadsheet is a tool that helps people manage their daily activities. They can be used to track finances, keep records, plan projects, etc. There are many different types of spreadsheets depending on the purpose that they are being used for.
0 Comments