Microsoft Excel
Excel’s SUBTOTAL function can show the SUM, AVERAGE, COUNT, MAX, MIN, and others functions. You should use the SUBTOTAL function to exclude values in hidden rows and get totals of totals. YouTube Video of using Subtotals Use Subtotal Function to calculate grand totals by Chris Menard - YouTube Table
Microsoft Excel
**Vertical analysis**, also called common-size analysis, takes the Sales and looks at each line items so that you can easily compare the income statements and balance sheets. Vertical analysis on an income statement will show the sales number (sometimes listed as Revenue) as 100%, and every other account will show
Microsoft Excel
A client sent me an Excel file they pulled from a database. I had to correct the dates, numbers, and text so I could do calculations. In this blog post, I cover: 1. T function 2. Text to columns 3. CTRL + H for replace 4. Trim function 5. Convert text
Microsoft Excel
You can create a named range that refers to a single cell in Excel. You can also select a group of cells on the worksheet and make it a named range. After you define Excel names, you can use the names in a formula, instead of using a constant value
Microsoft Excel
To find the break-even, you need to take your variable expenses and subtract them from your revenue or sales. This is knows as the Contribution Margin. **Variable** expenses are: 1. Direct material 2. Commission per unit 3. Hourly production wages 4. Shipping cost 5. Packaging Variable expenses are those expenses
Microsoft Excel
When you import data into Excel, occasionally a column of data will only have a key field listed one time. An example would be States only showing once at the start of each data set. See the image below. _Notice_ **column A**. It only displays the state for the first
Microsoft Excel
When you enter zip codes in Excel, the leading zeros will get dropped. Here are some examples. Massachusetts starts with 01 and 02. Hew Hampshire is 03, Maine is 04, Vermont is 05, Connecticut 06, and New Jersey is 07 and 08. So when you type in the zip codes
Microsoft Excel
Conditional formatting is one of my favorite features in Excel. I like it because I’m a visual person. Excel has a lot of predefined rules in Conditional Formatting, but it is very easy to write a formula if the predefined rules don’t have what you need. An Executive
Microsoft Excel
You can use data validation to restrict the type of data or the values that users enter into a cell. An example would be if you have a column for Departments, and someone puts in Accounting but another person types in Acct. (abbreviating Accounting). Data validation keeps bad data from
Microsoft Excel
There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, **absolute references** do not change when copied or filled. You can use an absolute reference to keep a row and/or column **constant**. The keyboard shortcut for absolute reference is
Microsoft Excel
Excel has built-in custom lists. You can sort data by days of the week or months of the year. Or create your own custom lists to sort by any other characteristic that doesn’t sort well alphabetically, such as July to June; October to September; S, M, L, XL. With
Microsoft Excel
When the numbers in your Excel file vary widely from data series to data series and you want to create a chart, the chart to create is a **Combo** chart. You would also use a combo chart when you have mixed types of data (for example, revenue and net income