In this post I will explain how to create an Access report that has subtotals and percentage formulas. The first step is to ask the question for which the report will answer. Lets look at the screenshot below and ask “Which country has the highest units sold?” and “What is the percentage of the total units sold per country?” We will group the report by country which has United States, Canada, Brazil and the Dominican Republic.
First click the Insert tab and chose report wizard.
Select the table(s) or query you want to work with.
Select the fields you want to be included in the report.
Group by country.
Select summary options.
Click sum and check the box to calculate percent of total for sums.
Name the report and click finish.
You could touch up the report by clicking on the design view.
In this post I will explain how to format certain rows in Excel with formatting that matches a condition set you a formula.
First lets study the sample dataset in the screenshot below.
Observe that the first row of data is row 2 because we have a header row. So that means I will need to start off selecting the dataset with row 2. Then I will choose the conditional formatting button from the home tab and click new rule. When the new rule dialog box displays, I will click “Use a formula to determine which cells to format”.
Lets say that I want to highlight an orange row for all records that sales were sold in a “store”. Column C has either “store” or “online”. It will be important to write the formula using the first row of data that references the cell of that row. The formula will be =$C2=”store”. The reason for the dollar sign in front of column C is that is will absolutely reference column C. I do not want a dollar sign in front of row 2 because I want that formula to go down relative to all the other rows till the end of my dataset. Not certain you understand the difference between $A$1 and A1 then check out my video post explaining when you use absolute vs relative.
Pick a format fill and then click ok.
Here is what I get. All the rows containing “store” will show up in orange.:)
Let me do another example
Ok in this next lesson I will format all the rows that are Fridays. I can format the dates to long date format just to peek at the days of the week to help the screenshots.
First I will select the dataset and then click the conditional formatting command to add a new rule. I will use the WEEKDAY() function which results in a value 1 to 7.
Monday = 2
My formula will be =WEEKDAY($A2)=6.
And my result is that all rows will turn green when the date is a Friday.
For more cool things you can do with dates use the DATEDIF formula. Details here.
Download my Excel keyboard shortcuts or Income and Expenses budget.
This post will teach you about a feature that is connected to the crop tool in Photoshop. Open the photo and then click the crop tool or hit the shortcut key “c”. Once you activate the crop tool you will have the “Straighten” command in the tools panel above.
Click “Straighten” and then draw a line with the mouse across the horizon you want to be the corrected. The photo will rotate and crop out the corners. Then click the check mark or hit enter to commit changes
Let’s say that we have a spreadsheet that records a purchase date for when a customer places an order and a ship date for when the product ships out. We would like to have a red flag in column A for orders that currently have not been shipped. And if the product has shipped we would like a green check mark. See the below the example and then keep reading and I’ll show you how to create this spreadsheet using either Excel 2007, 2010, 2013 or 2016. Note that row 6 and row 10 displays a red flag because those orders have not been shipped.
Step 1: Click in cell A2 and write a formula =IF(G2=””,-1,G2-F2). This IF function checks if the Ship Date is blank. Excel an verify if there is a blank cell by using the formula =”” so if G2=”” is true then make the value equal to -1. Otherwise, if the ship date is not blank, then use the formula to equal ship date minus purchase date [G2-F2]. The answer will be zero if the order has the same ship date as the purchase date. Note that on row 4 and row 7 that the formula equals zero because the ship date is equal to the purchase date which results in 0.
Step 2: Copy the formula down using the auto fill handle.
Step 3: With the status cells selected in column A, choose the Conditional Formatting command from the Home tab. Select the icon sets menu and then click the 3 symbols (Uncircled).
Step 4: Click Conditional Formatting –> manage rules –> Edit formatting rule.
Step 5: Click show icon only (Screen shot below is from Excel 2013. Will look a little different in Excel 2007)
Step 6: Green check mark icon when value is >=0 Number. (Replace percent with number from the type drown down). Type a zero in the value area.
Step 7: Click no cell icon for when < 0 and >=0 Number. ( the middle range yellow exclamation mark will be replaced with no cell icon.
Step 8: Change the red X with a red flag for when <0 and click OK.
Click here to download this practice file.
Download my Excel Keyboard Shortcuts here.
Data Modeling Steps in Excel Power Pivot
1.Import data into Power Pivot Data Model
2.Create relationships between the Dimension Tables and Fact Tables
3.Create DAX formulas (Data Analysis Expressions)
- DAX Measures are used in the Values area of a PivotTable
- Calculated Columns are used in the criteria for Rows, Columns, and Slicers
4.Hide Tables and Fields not to be used in the PivotTables reports
5.Create PivotTables & PivotCharts
6.Refresh when source data changes
7.Update the Data Model if necessary
The Vlookup function is an awesome function. It looks up information in a table where you have defined the lookup value in the left-most column of the table. In this video I share how to use it with an IF function after determining whether an individual has made their sales goal to determine their commission check.
Practice with my training file here. Also you might be interested in using the Match and Index functions as an alternative to the Vlookup if you have a large list of table columns.
Download my Excel Keyboard Shortcuts guide here.
Posted in Excel, Videos
The People Graph is an Excel 2013/2016 Add-In which makes graphic style charts. Find this command in the Add-Ins group of the Insert tab. Below is a video demonstration.
In this post I will share with you how to link an Excel chart to a PowerPoint presentation. This is helpful when you expect changes to be made to the Excel chart and you want to have your audience view those changes from in one of your PowerPoint slides.
- Right click the edge of the Excel chart and select copy.
2. Navigate to PowerPoint slide and select the Paste Special menu from the clipboard group.
3. Select Paste Link as Microsoft Excel Chart Object
4. Click File >Info and then click “Edit Links to Files” which is in the bottom right corner of PowerPoint 2010, 2013, and 2016 versions. Check box for “Automatic Update” in the Links box.
Note: It is important that the Excel file is saved to a location in which the PowerPoint can read the link otherwise the update cannot work. Typically you would save the Excel file with the chart to a network drive that is accessible to the file from which the PowerPoint is saved.
Click here to download my PowerPoint shortcuts.
In this post, I am going to explain how to connect Visio shapes. You can connect to the shape or to its connection point.
Posted in Videos, Visio