- Excel Tips & Tricks: My favorite formulas & features
- vlookup formula in Excel for Mac
- Using Excel for Project Planning
- Using Excel for Resource Planning
- How to copy an entire Excel worksheet (on Excel for Mac)
- Adding Commands to Excel Quick Access Toolbar (Excel for PC)
- MOST POPULAR POST: How to Freeze Panes in Excel for Mac
My co-worker Jonny recently told me that he used to hate Excel until he saw me use it. I think I’m still in shock that someone could hate Excel. I am a huge Excel nerd. There’s very very activities that I love more than getting lost in spreadsheets. A few years ago I converted to Mac at home. I love everything about Macs EXCEPT Excel for Mac. Compared to the PC version, using it feels like torture.
My favorite Excel features:
F4. Use the F4 key to repeat the last thing you did (i.e. if you just made a cell green, use F4 to make another cell green. If you just deleted 3 lines, use F4 to delete 3 more lines). If F4 isn’t working on your PC, make sure F-lock is disabled. This feature is a no-go in a Mac.
Format Painter. I hate wasting time on formatting. But thankfully, the format painter button means I only have to format once and then I can easily “copy” that formatting to other cells. I use format painter so often that I like to add it to my “Quick Access Toolbar”
Customizing the Quick Access Toolbar (only on the PC version). For weeks after I converted to the new ribboned version of Excel, I struggled to find the functions I needed. Thankfully they added the ability to customize the Quick Access Toolbar. There are 2 ways to do this: 1) Selecting the downward facing arrow on the top bar, selecting “more commands”, then look for the function you want. Here’s a step-by-step visual on how to do this. 2) Right clicking on the function then selecting “Add to Quick Access Toolbar”
Filtering. I often use Excel to track large amounts of information and filtering allows me to find the information I need.
- A tip I’ve learned the hard way: If you add a column after turning on filtering, double-check that the filter icon is showing up in the top cell. If it’s not showing up and you sort your spreadsheet, the information in that column won’t sort. To fix, just turn the filtering feature off and then back on.
Find/Replace. Find and replace is often a life-saver. Right now I’m putting together a marketing plan for 2013, but because it’s 2012, every date I enter defaults to 2012 unless I type in the year. By using find/replace I can quickly replace all 2012 dates with 2013. The speeds up my data entry. I like to use the short cut Command (or Control on PC) + F
- Using an asterisk makes it even more handy. If you type a comma and then an asterisk (,*) in the FIND box and then leave the REPLACE box blank, Excel will delete everything to the right of, and including, the comma. If you type an asterisk and a dash (*-) Excel will delete everything to the left of, and including, the dash. If you want to keep the dash, just type a dash into the REPLACE box.
Freeze Panes. When working with a lot of data, this feature is also incredibly helpful. It allows me to see the top and left cells even if I scroll to the bottom or far right of the worksheet. Learn how to use the freeze pane feature.
Copy/Move sheet. If you want to copy an entire sheet to another workbook (that is currently open) you can move the entire sheet, formatting and all, in a few clicks. Learn how.
A few features I really like, but that I don’t use very often:
- Text to column. I use this when I have information in one column that I want to split into multiple columns.
- Validation. This feature allows for more accurate data entry. I prefer to validate using a list that lives on a different worksheet.
- Remove duplicates. This button makes identifying unique values in a long list quick and easy.
- Group. This is great when I’m working with large groups of information that I want to condense or subtotal.
If you don’t mind “tech speak,” this blog has some tips and tricks that I love to use, plus a few that I was really excited to learn.
My Favorite Excel Formulas
In addition to the normal addition, subtraction, multiplication, division and sum formulas, these are the top 3 that I use that make my life easier.
=WORKDAY( I use the workday formula for planning purposes. It allows me to calculate when a project should start (if I have the due date) or when a project will be finished (if I know when I’m starting it) and it accounts for weekends and any holidays I want to exclude. Check out this post on how I use the workday formula to project plan.
=CONCATENATE( I use concatenate when I want the values of multiple cells to be “added together” into one cell or when I have additional text I want to add to other cells. This blog has a good step-by-step instruction for using the concatenate function.
=VLOOKUP( I use vlookup to compare information from multiple worksheets. Check out this step-by-step guide to using the vlookup formula in Excel.
On occasion I also like to use:
- =MIN( =MAX( and =AVE( I use these formulas when I want to know the average, minimum and maximum values of a group of cells
- =IF( The if formula is more advanced than most of what I covered here. There are a lot of uses for it, but I primarily do a =IF(A2=””,””,FORMULA) calculation that says, if cell A2 (or whatever cell I’m calculating off of) is blank, return a blank value. If it’s not blank, then do this FORMULA. And the calculation I want to do is nested where it says “FORMULA”.
- =PROPER( =LOWER( = UPPER( These 3 formulas will change the case of text in a cell to Proper Casing, lower casing or UPPER CASING.
- =COUNT( will tell you how many cells in a range contain a value
Excel formulas can get gnarly really quickly. Just do a google search and you’ll see what I mean. I always suggest keeping it as simple as possible. And to do a sanity test on the results of your formulas.
Other Excel related blog posts:
- Using Excel for Resource Planning (using stacked bar charts to make simple Gantt charts)
I recently discovered the value of vlookup in excel. And I keeping finding more and more ways and reasons to use it. Last week a co-worker needed to compare values on a report from two different dates. We were chatting about it and I realized the vlookup would solve her problem.
In this example, I have sales reports from 3 separate days.
I want a quick look at how much revenue I made, by product, over all 3 days.
Step 1: I want a consolidated list of products.
- Step 1a: I’ll copy the values from column A in all 3 sheets into a new workbook
- Step 1b: I’ll highlight the column
- Step 1c: I’ll go to the Data Ribbon and select Remove duplicates
- Step 1d: When it asks me, I’ll confirm that I want to remove the duplicates
Step 2: Create Formula. =VLOOKUP(A2,’Day 1′!A:D,4,FALSE)
What this says is in English is:
- Go to sheet Day 1 [‘Day1’!],
- Look at the information in columns A thru D [A:D],
- Find the row that says “Shoes” [A2]
- Then give me the value that is in column 4 in the row that says “shoes”
- And only display information if the value in A2 is EXACTLY the same [FALSE].
The logic for the formula is in this order: the value we’re looking for [A2], the place where we are looking to find it [‘Day 1’!A:D], the column with the information we want to display , and we want exact values [FALSE]
Step 4: Use the formula for column C, but link to Day 2’s worksheet.
Step 5: Use the formula for Column C, but link to Day 3’s worksheet.
Step 6: Copy values down for all rows.
This is what you should end up with. Here’s the spreadsheet if you want to practice. You’ll notice that this returned errors for the products that didn’t sell on certain days. There are two solutions to this.
SOLUTION 1: more steps, but less complicated
If you won’t be constantly updating the spreadsheet, you can do a quick and easy copy/”paste value” and find/replace:
Step A: Select all cells
Step B: Copy. I prefer Command (or Control on PC) + C
Step C: Right click, select “Paste Special”, then select “Values”
Step D: Do Find/Replace. I like to select Command (or Control for PC) + F, then select the “Replace” button. Type in #N/A in the Find box and leave the Replace box empty. Select “Replace All”
Now you have cleaned up data and you can run calculations or create charts with it.
SOLUTION 2: the more complicated formula solution
If you’ll be using this spreadsheet on an ongoing basis, I’d suggest an =IF( formula.
We’re starting with
=VLOOKUP(A2,’Day 1′!A:D,4,FALSE) and we’re going to change it to
What this says in English is: If there’s an error when doing this formula, then give me a blank cell.
I’ve used Microsoft Project in the past. There were some features I really liked, but mostly I found it frustrating, cumbersome and time-consuming. I hate wasting time; I have an obsession with being as efficient as possible. So a few years ago I ditched Project and decided to use Excel to handle my project management needs.
My solution is a combination of the =WORKDAY( formula and the stacked bar chart as a Gantt chart (as explained in this video and this post). In this post I’ll be talking about how I use the workday formula in Excel to calculate the key milestones for my projects all based off the due date and the number of days each activity will take.
For example: Let’s say I’m working on a promotional plan that will launch on March 1st and will run for 30 days. In order for the promotion to launch I need the following items:
- A landing page, which will take 1 week to code, 2 weeks to design and 1 week to write copy, plus 2 days between each for approvals and revisions.
- An email that is going out on launch day (3/1) that is due to the media company 5 business days before it sends, will require 3 days to code, 5 days to design and 2 days to write copy with 1 day at the end for final approval. Plus an additional email that is going out on 3/25.
- Banner ads that are due to the media company 3 business days before launch, and that require 7 business days in design plus 1 day for review/approval.
- Blog posts that will go out every week for the course of the campaign and that require about 2-3 days to write and 1 day to get proofed.
Plus, our company is closed 2/15-2/18 and no work will be accomplished on those days.
Here is the spreadsheet I created that calculates all the “start/due” dates for each activity. Below I’ll walk step-by-step through how/why I did what. Note: I always set up these type of spreadsheets as templates I can use on multiple projects, so in the spreadsheet I’ve highlight the only cells (in yellow) that should be changed when using for a different project.
- To start, I’ll enter the closed “holidays” in a separate tab/worksheet. The dates are in cells A1:A4.
- Then I’ll create a new tab called “Project Plan”. I’ll enter the start date (B2), the promotion length (B3) and then in B4 I’ll add B2 + B3 together to get the promotion end date
- In row 5 I’ll add the headers for each of my columns. Then I’ll use Freeze Panes to make it so rows 1-5 show up no matter how far down I scroll in the worksheet.
- In row 7 I added a header for my section “Landing page” by merging 3 cells together and highlighting the cells in gray.
- Then in Column A I outlined all the tasks in reverse order
- In Column B I added how long (in business days) each activity should take
- In C8 I linked to B1, which is the go live date.
- In C9 I created the =WORKDAY( formula. The logic for this formula is: the first value (C8 in my example) is the “start date”, the second value (B9 in my example) is the number of days we are adding the to the start date, and the third value is the holidays that I entered in step 1 (NOTE: I added dollar signs between the cell references so that I can copy the formula without breaking the holiday calculations). You’ll notice that instead of calculating from the date I’m starting my project, I’m calculating based on the date I want my project to end, so instead of adding days, I want to subtract them. That is why there is a minus sign in front of B9.
- I then copied this formula to C10 through C14.
- I repeated steps 4-9 for each of the different deliverables.
- On the blogs, I simply copied and pasted each section and then added 7 days to the previous “Go Live” date.
- Then, to make this spreadsheet more useful, I highlight rows 5 through 62 and then turn on the filter. Now, I can create a filter that shows me every activity that needs to be done in a particular period of time. Here’s an example of filtering so that only February dates and blank values show up (I keep the blank values so I can see the section headers).
Let me know if this was helpful. I know that I didn’t explain all the commands necessary to execute this type of spreadsheet, so let me know if additional details or posts will be useful.
Right now I’m planning every promotion we’ll be doing in 2013. This represents hundreds of different promotions that will be managed by more than a dozen people. I’ve been using Excel to build my list of promotions so that I could:
- Easily filter them by categories (huge vs mini promotion, sale vs product promotion, etc.)
- Filter them by date or date ranges
- Filter them by who will be running the promotion
- Identify the start or end date and then calculate the other date based on how long the promotion should last
The information is great, but I was having a hard time wrapping my mind around what was going on and how they overlapped. In these situations I find that visuals are incredibly helpful in identifying how much activity will happen in a certain period of time. What I needed was a Gantt chart without the complexity of Microsoft Project. I did a Google search (which is pretty much how I’ve learned to do everything in Excel) and found this YouTube video that taught me to do exactly what I wanted to do (not to mention the instructor is Irish and I LOVE accents!).
I ended up with a chart that was several hundred lines long, which was pretty but wasn’t particularly useful. As I played with different options, I realized that the chart only displays visible cells. So I could filter the data and get really useful visuals. This means I can filter by an individual and see what projects they own over the course of the year and how they overlap AND I can simply look at specific month and see how many promotions would overlap.
I was so excited that I just had to show some of my co-workers. It looks like we’ll be using this simple chart to give us visuals for several other planning-related activities. #soexcited
There’s a quick and easy way to move an entire Excel worksheet to a different workbook.
Side note: A workbook is what you’d typically think of as an Excel file. It can contain multiple work sheets. Worksheets are added, deleted and managed in the tabs at the bottom.
I’ve seen people copy entire worksheets using copy/paste. Which works fine, especially if you know the trick that clicking the box in the top left (right between A and 1) will highlight everything in the worksheet. However, this often means that you’ll need to reformat your column widths and row heights. A good solution to this problem is to copy the entire worksheet at once.
Step 1: Open both workbooks (the one with the worksheet you want to move and the one you want to move it to).
Step 2: Right-click on the tab for the worksheet you want to move. This will give you a menu. Select “Move or Copy”
Note: It’s possible to copy multiple worksheets at once, all you need to do is hold down Command (for Mac) and select multiple tabs BEFORE you right-click
Step 3a: Select the workbook you want to move your worksheet to
Step 3b: Select “Create a copy” (if you want to keep a copy in your current workbook)
Step 3c: Select ok.
Now your entire worksheet has been copied to another workbook.
When I switched to the ribboned version of Excel it was indescribably painful. I was quick and efficient at Excel and I knew exactly where I needed to go to get what I wanted. The ribboned version blew all of my knowledge out of the water. This interactive tool from Microsoft was very useful. But I also grew to love the Quick Access Toolbar and every time I go to use Mac for Excel I’m disappointed that I don’t have one.
Step 1: select the little down arrow next to the current Quick Access Toolbar options
Step 2: Select “More Commands”
Step 3: This should give you a screen like this:
There are a number of functions/commands that I like to utilize in Excel that I can’t ever find unless I add them to my quick access toolbar. The main one being the option to “Select Visible Cells”.
Step 1: Grab the bar on the far right side and drag down
Step 2: Drag until the top bar to below the cells you want frozen at the top
Step 3: Grab the bar on the bottom right and drag to the right.
Step 4: Drag the bar until it is to the right of the cells you want frozen on the left.
Step 5: Freeze the panes. On a Mac go to the Window menu and then select freeze pane. On a PC, you’ll go to the View menu. One nice time saver on the PC version is it has the option on the View / Freeze Pane menu to freeze the first row and/or the first column.
UPDATE: As a response to this post, my friend Michelle asked how to make the same rows print at the top of every page. Go to Page Set-up and select the “Sheet” tab. Click the strange button to the right of the “Rows to repeat at top:” box and then select the row(s) you want. The option to repeat columns on the left is right below if you want to do that.