You can quickly sum a block of data (both Rows and Columns) in Excel with just one click.
Filling down an Excel Formula in a column can be quickly achieved by positioning the cursor on the lower right corner of the cell and Double Clicking.
The formula will repeat to the end of the data.
Excel Formulas & Functions are covered in these Excel classes:
See our other Excel topics.
Hiding certain rows or columns in Excel can make viewing complex, detailed information easier to read. Grouping Rows in Excel allows you to quickly collapse and expand similar data to create more useful views.
See our other Excel topics.
Excel Vertical text is an easy way to label groups of rows and make your charts more readable. This only takes a couple of clicks with Excel.
Start by adding a column to the left of your chart. Then select all of the cells in that column that are to the left of a group of rows. In this example, I selected the cells to the left of the mammals. Choose Merge and Center under the Alignment group under the Home tab.
Next, type into the newly merged cell your data label. Click on the Text Orientation button in the Alignment group and choose Vertical. You may also want to choose Wrap Text and the Center Alignment options.
Adjust the width of the column and add Cell styles as needed to decorate your Excel vertical text.
Excel Formatting is covered in these Excel classes:
See our other Excel topics.
Have you ever imported data that was in all caps, or all lower case? There is a quick trick for capitalizing the first letter of each word in Excel.
In this example, I copy and pasted a list of the 3 most expensive paintings of all time.
The 'Proper' function allows me to quickly transform the data so that every word is capitalized. Simply type =proper(The cell you need to change).
Excel Formulas & Functions are covered in these Excel classes:
See our other Excel topics.
Excel Conditional Formatting is covered in these Excel classes:
See our other Excel topics.
Because of importing or dubious data entry, many users have to deal with worksheets that have improperly parsed data. Most commonly, this means that a single cell might contain data that should be divided over several columns. Excel has a few features for parsing this data into several cells.
If the data in a single cell is divided by spaces, commas, or some other consistent character, then dividing that data into individual cells is fairly simple.
Select the cells you wish to parse. Then click on the Data tab and select Text to Columns under the Data Tools group. This will start the Text to Columns Wizard.
In this example, the address, city, state, and zip code are delimited by commas, which is common for an import, so with the radial button Delimited selected, click Next.
Identify you delimiter by checking the appropriate box or boxes on the left. When the data in the preview window is correct, click Next or Finish.
The Next button will allow you to choose the data format for each of the columns. This may not be necessary. Click Finish when you are ready.
Your data will be parsed across the columns to the right.
In some cases, parsing text from a cell may be easier with a fixed width setting.
Start by selecting the cells you wish to parse. Then under the Data tab in the Data Tools Group, choose Text to Columns. In the Wizard dialogue box choose the radial button Fixed Width and choose Next.
Use the preview window to select the width of your data by dragging the line to the proper position. Click Next to identify the data format or Finish.
In Excel the MID function allows a user to extract data from the center of a cell. The syntax for this formula is:
=MID({cell reference}, {order number for the first character desired}, {number of characters to include})
=MID(...) calls up the formula.
{Cell reference} is the cell that has the data.
{order number for the first character desired} refers to the character number for the first letter desired. In the example above, this number is 7, referring to the 7 characters in the word AMOUNT_.
{number of characters to include} refers to the number of characters that should be extracted. In the case of the example, this number is 5, as in the 5 characters in $2304.
Excel Formulas & Functions are covered in these Excel classes:
See our other Excel topics.
Recording a macro with a complex sort is actually very simple.
Start with your data unsorted and unselected. Then under the View tab, click on the down arrow just below the Macros Icon and choose Record Macro.
Type a Macro name that you can remember, and assign a shortcut key if you wish by typing a letter next to "Ctrl +". You can also add a description, which is helpful if you have a number of different sorts. Then click OK.
Once you click this button, Excel watches everything you do, and can repeat those actions automatically. Sort your document, but be careful not to make any edits or do anything you wouldn't want repeated.
When you're finished, click on the down arrow again and choose Stop Recording.
To test the macro, resort your data to change the way it looks. Then, either hit the shortcut key you programmed, or click on the down arrow under Macros again, and choose view macro. Select your desired macro from the list and choose run.
See our other Excel topics.
Excel has tools for complex, or multilayered sorts that involve several columns or headers. There are 2 ways to create a complex sort in Excel.
Select the all of the data in the table you need to sort. Under the Home tab, click on Sort & Filter in the Editing Group. Then choose Custom Sort (this feature can also be accessed with the Sort button under the Data tab in the Sort and Filter group). A dialog box will pop up.
Check or uncheck the My Data has Headers box, depending on your data. Then change Sort by to the column that has the data that should be sorted first. Use the Add Level button to add other criteria to the sort. You can position the order of sorting with the up and down arrows.
In this example, the data will first be sorted by Favorite Color, then by Favorite Animal, then by Order.
The other way to create a complex sort, meaning sorting by multiple columns, is to use the filter tool.
Select your data. Then, under the Data tab, toggle on Filter under the Sort and Filter group. Drop down arrows will appear in each of your columns.
These drop down arrows offer a few different tools. You can filter the rows by value or color, and you can also sort alphabetically or sequentially either direction.
Use the sorting tools in order to organize your data the way that you need it to be organized, sorting and resorting the data in the appropriate order.
After programming a complex sort in Excel, it is often helpful to save that sort to use again on the same table. You should be able to do this automatically as the sort settings are saved with the document. Simple select the exact same cells and click on the sort button again. If your saved sort isn't offered, try selecting the entire worksheet with Control + A, and then clicking on the sort button.
You can also save a sort in Excel with a simple macro.
Excel Formulas & Functions are covered in these Excel classes:
See our other Excel topics.
Small formula errors can have big consequences. A misplaced comma or the wrong reference can severely warp your results. Excel has several tools to check your formulas for errors.
To check the formula of a specific cell, select the cell, then hit F2 on your keyboard. The cells referenced in the formula will be highlighted, and you will be able to edit the formula without a mouse click.
Select any cell in your spreadsheet and on your keyboard hit Control and the Tilde ( ~ ) sign at the same time. This will show all of the formulas in the worksheet. Toggle back to the normal view by hitting Control + ~ again.
You can also access this tool in the Formula Auditing group under the Formula tab. Toggle Show Formulas on and off to see the formulas in the workbook.
In Excel, a precedent is a cell being reference in a formula. If a formula is =C6+7 then C6 is a precedent. A dependent is a cell that depends on the data from another cell for its content, so if the formula above were typed into cell C9, then C9 would be a dependent of C6.
To display precedents, select the cell with the formula and click on the Formula tab. Then choose Trace Precedents in the Formula Auditing group. Arrow will appear showing which cells are referenced in the formula. Toggle this off by clicking on Remove Arrows in the same group.
Select a cell that is referenced in the formulas of other cells. Under the Formulas tab, in the Formula Auditing Group, click on Trace Dependents. Arrows will appear showing the cells that depend on that cell.
Excel Formulas & Functions are covered in these Excel classes:
See our other Excel topics.
