**Excel Tricks**

**1: Use the fill handle to copy formatting**

The fill handle is a versatile and powerful tool. Besides copying formulas and creating series, the fill handle can copy formats with just a few quick clicks:

Select the cell that contains the formatting you want to copy. In Figure A, I’ve selected A2 to copy the bold font and gray fill color to the remaining cells in column A.

Double-click the cell’s fill handle. The fill handle’s series behavior has kicked into gear by overwriting the TOTALS label with January. Don’t worry about that, you can undo that next.

Click the resulting Auto Fill Options control to display the list shown in Figure B.

Select the Fill Formatting Only option.

**2: Use Paste to copy formatting**

Another quick copy trick utilizes the Paste feature. Again, the Format Painter works great with a small range, but this trick is helpful when copying formats to an entire column or row:

Select the source cell and press [Ctrl]+C.

Click anywhere inside the destination column or row.

Press [Ctrl]+[Space-bar] to select the entire column or [Shift]+[Space-bar] to select the entire row. (This works only with a blank data range.) 2010: With the column or row selected, choose Formatting from the Paste drop-down (in the Clipboard group). 2007: Choose Paste Special from the Paste drop-down and click Formats in the Paste section. 2003: Right-click a selected cell and choose Paste Special from the sub menu. In the resulting dialog, click Formats in the Paste section.

Using Live Preview, you can see what the applied formats will look like. Click OK if you decide to apply them.

You can also format a new chart using Paste. Select the source chart and press [Ctrl]+C. Select the destination chart and choose Paste Special from the Paste drop-down. Choose Formats and click OK.

**3: Copy styles between workbooks**

If you use the same custom cell styles in multiple workbooks, don’t spend time re-creating each style. Instead, copy the style from one file to another as follows:

Open the source workbook and a destination workbook.

From the destination workbook, click Cell Styles in the Styles group on the Home tab. In Excel 2003, choose Styles from the Format menu.

Choose Merge Styles at the bottom of the gallery.

In the resulting dialog, select the open workbook that contains the styles you want to copy.

Click OK twice.

If you want all new workbooks to share the same custom style, open Excel’s default workbook, book.xltx as the destination. (Open book.xlt in Excel 2003.) Add the style, then save and close the template file. All new workbooks based on book.xltx will contain the merged styles.

**4: Create a custom format for readable data**

Numbers with a few digits are easy to read. Once you drop in that second thousands separator, numbers become less readable, especially if your data contains lots of them. Fortunately, a custom format can reduce the number of digits, making them easier to read, but without changing the scale. To illustrate, we’ll apply this custom format to the values in the bottom range (so you can compare):

Select B9:E13 and click the Number group’s dialog launcher or press [Ctrl]+1 to display the Format Cells dialog.

From the Category list (on the Number tab) choose Custom.

In the Type control, enter the $#.##,,” M”; format string, as shown in Figure D. The pound sign combined with the two comma characters displays a character in the millions position, if one exists. The M component displays a literal M character, to denote millions.

Click OK to see the results in Figure E.

**5: Create a cell style that indicates purpose**

Using a Cell Style to identify purpose helps users acclimate quicker. It also provides an easy way to ensure consistency in an organization. For example, you might use color to distinguish input and label cells. Using a Cell Style is an efficient way to put that convention to work. Let’s illustrate this concept by creating a Cell Style for input cells:

Click the Home tab and then click Cell Styles in the Styles group. In Excel 2003, choose Style from the Format menu and skip to #3.

Click New Cell Style at the bottom of the list.

In the resulting dialog box, enter a name for the style, such as InputCell.

Click Format. In Excel 2003, click Modify.

Click the Border tab and choose the Outline option in the Presets section.

Click the Fill tab and choose light blue.

Click OK to view the selected formats shown in Figure F.

Click OK again.

Anytime you want to indicate an input cell or range, do the following:

Select the cell.

On the Home tab, click the Cell Styles option. In Excel 2003, choose Style from the Format menu.

Click InputCell as shown in Figure G and Excel will apply that style to the selected cell or range. In Excel 2003, choose InputCell from the drop-down list and click Add.

Using a Cell Style is efficient for the workbook’s author, but it also helps users quickly identify a cell or range’s purpose.

**6: Copy formats quickl**y

An efficient copy technique is a good tool, especially if you can choose what to copy on the fly. To do so, select the destination cell or range. Then, right-click the border and drag it to the target cell. When you release the mouse, Excel will display the submenu shown in Figure H. Choose the Copy Here As Formats Only option. That was easy!

**7: Add a background image**

Adding a background image to a sheet is so easy that you might be tempted to spruce up all your sheets this way. (You’ll refrain from doing so, of course.) To add an image to a sheet’s background, do the following:

Click the Page Layout tab.

Click the Background option in the Page Setup group.

Browse to the file and double-click it.

**8: Quickly apply table formatting**

If you select a range and choose a built-in format from the Format As A Table drop-down, Excel (2007 and later) converts the range to a Table object. If the format works for you, but you don’t want a Table object, you can keep the format and dump the Table. Doing so takes a few clicks, but probably fewer than formatting manually. To format the data range quickly using a built-in Table format, do the following:

Click anywhere inside the data range.

On the Home tab, click the Format As Table drop-down and choose a format from the gallery.

Choose appropriately when Excel asks if the range has headers and click OK.

Click anywhere inside the table.

With the contextual Design tab current, choose Convert To Range in the Tools group.

Click Yes to confirm the action.

You’ll format a data range with only six clicks (or a few more depending on how many times you click the thumb in the gallery).

**9: Save formats as styles**

When you use the Number Formats drop-down in the Number group (on the Home tab), you’re actually applying a style — a style you can control. For instance, the Percent style displays two decimal values, and you might want to inhibit all decimal values for percentages. To do so, click the Number group’s dialog launcher, click the Number tab, choose Percentage, change the Decimal Places value to 0, and click OK.

We tend to think of these styles as formats set in stone, but they’re not. Modify them to suit your needs. Styles are available only to the workbook in which you save them, but you can modify the styles in your templates.

**10: Format as you go**

For a quick one-time solution, you can format some values as you enter them:

To enter currency, type a dollar sign ($) before typing the value to apply the Currency format.

To enter most fractions, type 0. Then, press the spacebar and type the fraction, including the slash. Excel will display the value as a fraction and store the decimal value.

To enter a percentage, simply follow the value with a percent sign.

**11: Automatically SUM() with ALT + =**

Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT + ‘=’ (equals key) to add up the numbers in every cell above.

**12: Logic for Number Formatting Keyboard Shortcuts**

At times keyboard shortcuts seem random, but there is logic behind them. Let’s break an example down. To format a number as a currency the shortcut is CRTL + SHIFT + 4.

Both the SHIFT and 4 keys seem random, but they’re intentionally used because SHIFT + 4 is the dollar sign ($). Therefore if we want to format as a currency, it’s simply: CTRL + ‘$’ (where the dollar sign is SHIFT + 4). The same is true for formatting a number as a percent.

**13: Display Formulas with CTRL + `**

When you’re troubleshooting misbehaving numbers first look at the formulas. Display the formula used in a cell by hitting just two keys: Ctrl + ` (known as the acute accent key) – this key is furthest to the left on the row with the number keys. When shifted it is the tilde (~).

**14: Jump to the Start or End of a Column Keyboard Shortcut**

You are thousands of rows deep into your data set and need to get to the first or last cell. Scrolling is OK but the quickest way is to use the keyboard shortcut CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell.

When you combine this shortcut with the SHIFT key, you’ll select a continuous block of cells from your original starting point.

**15: Repeat a Formula to Multiple Cells**

Never type out the same formula over and over in new cells again. This trick populates all of the cells in a column with the same formula, but adjusts to use the data specific to each row.

Create the formula you need in the first cell. Then move your cursor to the lower right corner of that cell and, when it turns into a plus sign, double click to copy that formula into the rest of the cells in that column. Each cell in the column will show the results of the formula using the data in that row.

**16: Add or Delete Columns Keyboard Shortcut**

Managing columns and rows in your spreadsheet is an all-day task. Whether adding or deleting, you can save a little time when you use this keyboard shortcut. CTRL + ‘-‘ (minus key) will delete the column your cursor is in and CTRL + SHIFT + ‘=’ (equal key) will add a new column. From an earlier tip, think about CTRL + ‘+’ (plus sign).

**17: Adjust Width of One or Multiple Columns**

It’s easy to adjust a column to the width of its content and get rid of those useless ##### entries. Click on the column’s header, move your cursor to the right side of the header and double click when it turns into a plus sign.

**18: Copy a Pattern of Numbers or Even Dates**

Another amazing feature built into Excel is its ability to recognize a pattern in your data, and allow you to automatically copy it to other cells. Simply enter information in two rows which establish the pattern, highlight those rows and drag down for as many cells as you want to populate. This works with numbers, days of the week or months!

**19: Tab Between Worksheets**

Jumping from worksheet to worksheet doesn’t mean you have to move your hand off the keyboard with this cool shortcut. To change to the next worksheet to the right enter CTRL + PGDN. And conversely change to the worksheet to the left by entering CTRL + PGUP.

**20: Double Click Format Painter**

Format Painter is a great tool which lets you duplicate a format in other cells with no more effort than a mouse click. Many Excel users (Outlook, Word and PowerPoint too) use this handy feature, but did you know you can double-click Format Painter to copy the format into multiple cells? It’s quite a time-saver.

**21. No Excel? No Problem**

If you are working on a computer that doesn’t have MS Office and someone sends you an Excel file, you can still view the spreadsheet using Microsoft Excel Viewer, which you can get from Microsoft’s website. This is an excellent tool for viewing Excel files and you don’t have to purchase anything. You won’t be able to edit spreadsheets or create new ones using the viewer, but at least you will be able to see all contents of the file in an easily viewable format.

**22. Counting Sums**

The “count” and “counta” functions allow you to find the sum of numbers. The count is for numbers and “counta” for everything but numbers, such as names. To use the count function, click on the cell you want your count in, type “=count(” and then select the range of cells you want to capture. Then close the function with a closing “)” and hit Enter. To use the counta function, follow the same rules but replace “count” with “counta.”

**23. One Font to Rule Them All**

There is nothing worse than opening up a spreadsheet and seeing a hodge-podge of fonts thrown together. Unless you are developing a file to display all the different fonts Excel has, make sure you stick with one font throughout. This is more a housekeeping item than a hidden secret, but your coworkers will appreciate the consistency of your work.

**24. Use Keyboard Shortcuts**

Shortcuts are essential to using Excel. These days it’s not enough to know copy, paste and save; you need to know even more. Here are a few examples of easy keyboard shortcuts:

Ctrl + 2: Bold

Ctrl + 3: Italic

Ctrl + 4: Underline

Ctrl + 5: Strikethrough

Shift + Ctrl + F: Font dropdown list

Ctrl + 9: Hide rows

Ctrl + 0: Hide columns

Ctrl + Shift + (: Unhide rows

Ctrl + Shift +): Unhide columns

Shift + Space: Select entire row

Ctrl + Space: Select entire column

**25. Calculating Discounts**

Whether you are determining your earnings or calculating a discount, you can set up Excel to help you determine the percentage discounts. Enter the original price in cell A1, percent discount in cell A2 and discounted price in cell A3. In cells B1 through B3, you will enter the original price of the item and the percentage discounted. In cell B3, enter the formula (B2-A2)/A2 then click the % icon to format the number into a percentage.

**26. Color Coding for Emphasis**

This really isn’t a secret, but there are many times when a spreadsheet can be improved if you use colors to separate sections. Colored backgrounds work well with borders to help distinguish headings from data such as subtotals, totals, months, etc. However, you should avoid using color combinations that aren’t professional such as a lime green background with a purple font. Backgrounds should be a pale shade, but use common sense when choosing your color patterns. You don’t want to cause others to get a headache.

**27. Creating Call Logs**

One of the most useful features about Excel is the ability to organize data. Being able to organize names or clients is useful if you spend a lot of time making and receiving calls. It’s very simple to create a call log using Excel – and easier than trying to use Word. Create a worksheet with three simple headings: Name, time of call and reason for call.

**28. Filtering Your Results**

Let’s say you have a large spreadsheet with loads of information on it. Using Auto Filter to filter data is a quick way to find and work with a subset of data in a range of cells and it lets you choose what you want to see and what you want to exclude. Click the cell in the top left corner, highlighting the whole spreadsheet. **Click Data > Filter > Auto Filter** and you will see there are now little boxes in each column that help narrow your search. You can also use a Custom filter and just type in the information you want to locate.

There may be a time when you need to make a report that can’t be manipulated or edited. This is where PDFs come in handy. But if you don’t have Adobe Acrobat, you can use Excel to create your own PDF file. When you click File > Send you can Email as PDF Attachment. You can also save the file in PDF format. This enables you to create PDF-like files that aren’t editable. If your current version of Excel doesn’t have this option, you can download the PDF add-in from the Microsoft website.

**29. Importing Data From a Website**

If you come across a site with loads of data that is useful for a project you are working on, you can convert this into a spreadsheet. Click File > Open then change the file format to All Web Pages. This covers all .htm, .html, .mht and .mhtml files. Load the web address and click Open. Once you’ve done this, you may need to alter the formatting, but this is much easier than creating a new workbook.

Excel Formulas

Don’t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.

**Excel Formulas You Should Definitely Know:**

**1. SUM**

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula.

The above shows you different examples. You can have numbers in there separated by commas and it will add them together for you, you can have cell references and as long as there are numbers in those cells it will add them together for you, or you can have a range of cells with a colon in between the 2 cells, and it will add the numbers in all the cells in the range.

**2. COUNT**

Formula: =COUNT(A1:A10)

The count formula counts the number of cells in a range that have numbers in them.

This formula only works with numbers though:

It only counts the cells where there are numbers.

**Learn more about the COUNT function in this on-demand, online course. FREE preview**

**3. COUNTA**

Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.

The COUNTA Formula works with all data types.

It counts the number of non-empty cells no matter the data type.

**4. LEN**

Formula: =LEN(A1)

The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.

Notice the difference in the formula results: 10 characters without spaces in between the words, 12 with spaces between the words.

**5. TRIM**

Formula: =TRIM(A1)

Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.

I added in an extra space behind “I Love Excel”. The TRIM formula removes that extra space. Check out the character count difference with and without the TRIM formula.

**6. RIGHT, LEFT, MID**

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)

These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.

I used the LEFT formula to get the first word. I had it look in cell A1 and grab only the 1st character from the left. This gave us the word “I” from “I love Excel”

I used the MID formula to get the middle word. I had it look in cell A1, start at character 3, and grab 5 characters after that. This gives us just the word “love” from “I love Excel”

I used the RIGHT formula to get the last word. I had it look at cell A1 and grab the first 6 characters from the right. This gives us “Excel” from “I love Excel”

**7. VLOOKUP**

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”. (See the full explanation of VLOOKUP) Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).

Note: If at all possible use a number for the lookup_value. This makes it a lot easier to make sure the data you are getting back is a correct match.

If it finds a match of the “lookup_value” in the left column of the “table_array” it will return the value in the column you specify using the “index_num”. The “index_num” is relative to the left most column. So, if you have the table_index look in column A and you want what is returned to be what’s in column B the “index_num” would be 2 because the leftmost column, column A in this case, is the 1st column in the table array and column B is the 2nd column (hence the 2 for the index number). If you want what is in column C to be returned you’d put 3 for the index_num. The “range_lookup” is a TRUE or FALSE value. If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match. I only use FALSE when using the VLOOKUP formula.

**Example:**

You have 2 lists: 1 with a sales person’s ID and the sales revenue for the quarter. Another with the sales person’s ID and the sales person’s name. You want to match up the sales person’s name to the sales person’s revenue numbers for the quarter. They are all jumbled around so to manually match this, even for a small number of salesmen would leave room for a high margin of error and take a lot of time.

The first list goes from A1 to B13. The 2nd list goes from D1 to E25.

In cell C1 I would put the formula =VLOOKUP(B18, $A$1:$B$13, 2, FALSE)

B18 = the lookup_value (the sales person’s ID. This is a number that appears on both lists.)

$A$1:$B$13 = the “table_array”. This is the area I want the formula to search the leftmost column (column E in this case) for the “lookup_value”. I went to F because if it finds match in column E, I want it to return what’s in column F. (The money signs are there so that the table_array will stay the same no matter where the formula is moved or copied to. This is called an absolute reference.)

2 = the index_num. This tells the formula the number of columns away from the left most column to return in case of match. So, if you find a match between the lookup_value and the leftmost column of the table array, return what’s in the same row in the 2nd column of the table (the 1st column is always the leftmost column. It starts at 1, not 0).

FALSE= tells the formula I want it to only return the value if it’s an exact match.

I would then copy and paste that formula along all the cells in column C next to the first list. This would give me a perfectly aligned list with the sales person’s ID, sales person’s revenue for the quarter, and the sales person’s name.

In order to get a nice neat list of Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each other I used the VLOOKUP formula to compare 1 list to another.

This is a complicated formula, but an extremely useful one. Check out some other examples: Vlookup Example, Microsoft’s Official Example.

**Learn more about the VLOOKUP function in this on-demand, online course. FREE preview**

**8. IF Statements**

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.

Continuing with the sales example: Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota” (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially when you get to more complicated things like Nested IF Statements in Excel).

It would look like this:

In the example with the VLOOKUP we had the revenue in column B and the person’s name in column C (brought in with the VLOOKUP). We could put their quota in column D and then we’d put the following formula in cell E1:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.

Having the result right there from the IF statement is a lot easier than manually figuring this out.

**9. SUMIF, COUNTIF, AVERAGEIF**

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.

I use these formulas in our example to see the average revenue (AVERAGEIF) if a person met their quota, Total revenue (SUMIF) for the just the sales people who met their quota, and the count of sales people who met their quota (COUNTIF)

**10. CONCATENATE**

A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in cell A1 and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Steve Quatrani”

Finding The Right Excel Formulas For The Job

There are 316 built in functions in Excel. You’re not going to sit there and memorize what all of them do (or at least I hope not!). Luckily Excel has a built in wizard that helps you find the correct formula for what you’re looking to do (if there is one).

Click the “fx” next to the formula bar in Excel

This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the correct excel formula:

I typed in “remove extra spaces” and it returned the TRIM formula that we went over earlier.

**More Excel Formulas**

There is so much more that I use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR, DAY, etc.), other formulas like AND and OR, along with many others.

The real power comes in combining these functions into complicated excel formulas.

Learn Excel Formulas Today!

Breaking Down Complicated Excel Formulas

=IFERROR(TRIM(IF(LEN(VLOOKUP(

Do you see formulas like the one above and run away screaming AHHHHHHHHHHHHHHHHHHHHHHHHH!!!

Well I don’t blame ya!

This can be a really intimidating formula even for the most seasoned Excel user.

I have a solution for you that makes it easy to both break down, and build up complicated combinations of Excel formulas but first check out this short video by fellow Udemy trainer, Mynda Treacy, that will reveal tips on how to get inside the mind of an Excel formula:

So What’s Going on With This Excel Formula?

Let’s break down all the Excel functions in it:

IFERROR

TRIM

IF

LEN

VLOOKUP

SUBSTITUTE

I’m not going to go in depth into each formula, but you’ll get the point.

Let’s start with the innermost formula. This is the end result that you are trying to accomplish.

We want to take a phrase (that we get with a VLOOKUP) that has a space in it and SUBSTITUTE that space with nothing. So it will take a phrase like “Excel Formula” and make it “ExcelFormula”.

SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”)

We use the VLOOKUP formula to get the actual phrase we want from a different sheet in the Excel Workbook if it matches what’s in cell F7.

VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)

We use an IF statement to say IF the number of characters (LEN) of what’s returned from the VLOOKUP is above 0, then run the VLOOKUP, otherwise, put nothing here. This way if the VLOOKUP returns nothing, then nothing will happen and this will prevent most errors.

IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(

We then wrap it with the TRIM function which gets rid of all extra spaces, besides 1 space in between words. Sometimes when you’re getting data from a database extra spaces can be added after a word. This can make it really hard to compare data from different sources, so the TRIM function comes in handy a lot!

TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(

Finally we wrap it in the IFERROR formula. This formula will be triggered if the other formulas wind up giving you an error. It could look something like #N/A and that’s pretty ugly. This can happen for legitimate reasons, but you don’t want to hand your boss a sheet full of #N/A symbols.

What happens is IF the combined formulas return any excel error, it will make the cell blank, otherwise it will show the results of the formula.

=IFERROR(TRIM(IF(LEN(VLOOKUP(

There you have it, this long complicated formula broken down into simple step by step functions.

You can use the same method for building up a long formula

Our goal is to SUBSTITUTE the spaces for nothing so we start with that. We then have to pull it in from another sheet so we use VLOOKUP for that, etc., etc.

You always start with the innermost formula, the end result that you are looking for. Then you use other formulas to help you deal with the different situations you’ll have to deal with working with large datasets.

Resources to Become an Excel Expert:

This article is the tip of the iceberg. Check out these other resources for more tips.

Resources For Showing Data:

Excel Formulas will help you deal with data, how you present that data is a whole other animal. Check out this awesome Excel Dashboard course if you’re interested in using Excel for reporting.

Resources For Excel Help:

Mr. Excel

No matter how good you get at Excel there is too much for one person to be great at everything Excel can do. The Mr. Excel forums are the place to go for any questions. They have a very active community around that site and you can probably get your excel questions answered very quickly.

OzGrid

There are a ton of examples for how to do just about anything in Excel or VBA on Ozgrid.

Learning Excel Macros and VBA

Formulas are awesome by themselves. Using VBA to create Excel Macros in combination with Excel Formulas is how you become a true Excel expert.

I completely automated a business analyst job I had at a fortune 500 company using these methods.

How I Became the “Excel Expert”

It was my 3rd year in college and I went to intern for an options trading firm. There were some brilliant people at this place and even the janitors knew Excel better than I did. The intern in there before me was a software engineer who had basically written a ton of macros that automated almost every daily task I had to do. Great! I thought, I’d have the easiest time doing the daily boring tasks and I could spend time learning how to be a trader.

Well, the week after he left, EVERYTHING BROKE. None of the macros were working and they all depended on each other. “I was never trained on this” I thought, because the software engineer had only told me what buttons to push and when, never how to do it manually. I started giving out wrong information, needing to ask busy people to take time to help me, and was just generally helpless. I did the best I could, but I didn’t even know most excel formulas, much less how to read and write VBA code and create Excel Macros.

Necessity, the Mother of Invention

Then one day I was taken aside and my manager said, “I know the kid before you did all this stuff, and you weren’t brought here to write code, but if you can’t fix it and start giving us the correct information on time, we’re going to have to go in a different direction.” WHAT?! I thought. I hadn’t been prepared or trained for this, and code that another person wrote is not working, and I’m going to get fired for it? I knew he meant it. I had seen interns get fired from here, it wasn’t pretty. It was an open trading floor, you didn’t get pulled aside into someone’s office, you were unceremoniously escorted off the floor and everyone knew what happened.

So, I had a goal to meet, and I had to meet it quickly. I set off to learn VBA, Excel Formulas, and how to get all these Excel macros working. After about 2 weeks of waking up at 5:30am, struggling through the day with the help of others, then teaching myself Excel until about 2am, I had learned enough to read the code that was written. After about a month I became proficient at it. After about 6 months I knew enough to write any Excel formula I wanted, read & write VBA code, and solve any Excel problem I had without doing manual work in Excel.

Learning it was a pain in the butt! There was no single resource to learn what I needed to learn. I had to go all over the web to find things or ask questions on Mr. Excel. That’s why I created a course on learning Excel. I put all my knowledge gained over the last 5 years, all my tips & tricks into this Excel VBA tutorial.

About the Author:

Steve Quatrani is an excel expert with years of experience using and teaching Excel. He has created advanced systems using VBA code, advanced dashboards using Excel and other business intelligence software, and he continues to build new ways of viewing and analyzing data in Excel and automating those processes. He teaches Microsoft Excel VBA Video Tutorial on Udemy.

From Steve:

I was astounded by how little some people knew about Excel and they use it every day! I decided to start teaching people how to use Excel and utilize the true power of this program to make their lives easier. Throughout my video courses I teach you everything you need to know to become the Excel expert, to spend less time doing stuff manually Excel, and to make your life a whole lot easier all using Excel.

## Leave a Reply