arabiaenergylife.blogg.se

1. Best excel for mac tutorial how to#
2. Best excel for mac tutorial full#
3. Best excel for mac tutorial code#

Now, we want to copy and paste these as values (Ctrl + C and then Alt, E, S, V) – this is because we want to delete the old data.

We can then copy both these formulas, select to the bottom with Shift + Ctrl and the arrow keys, and then use Alt, E, S, F to paste these as formulas: To fix these issues, we can use the PROPER and TRIM functions for the City and UPPER and TRIM for the State: The City and State columns have extra spaces at the beginning and end, as well as improper capitalization.

Best excel for mac tutorial code#

We can fix the ZIP Code formatting by selecting the whole column with Shift + Ctrl + Down, pressing Ctrl + 1, and then going to Number, Special, and Zip Code:

Best excel for mac tutorial how to#

How to Clean Data in Excel, Part 3: Fixing Each Column We can now delete the original data column, select all the new data, and then cut and paste it into the original column (E):Īt this point, we can also change the title of each column to something more appropriate, such as Address, City, State, and ZIP. We select cell F3, right next to the original data, for the destination cell, and then click “Finish”: We start this process by inserting a few extra columns to the right of the Address, City, State, and ZIP column, and then using the Text to Columns function (Alt, A, E) to separate the data based on the commas: =SUBSTITUTE: Replace text within text based on search (same in Mac Excel) How to Clean Data in Excel, Part 2: Adding Columns and Text to Columns =UPPER: Makes text all uppercase (same in Mac Excel)

=TRIM: Removes extra spaces (same in Mac Excel) =PROPER: Makes first letter in each word uppercase (same in Mac Excel) Shift + Arrow Keys: Select Multiple Cells (same in Mac Excel)Īlt, I, C: Insert Column (Ctrl + Shift + + in Mac Excel)Īlt, O, C, W: Change Column Width (No equivalent in Mac Excel)Īlt, O, C, A: Auto-Fit Column Width (No equivalent in Mac Excel)į2: Edit Cell (same in Modern Mac Excel may need Ctrl + U in older versions)Īlt, E, D: Delete Cells/Rows/Columns (Ctrl + – in Mac Excel) Copy/Paste/Formatting Shortcuts:Ĭtrl + 1: Format Dialog Box (⌘ + 1 in Mac Excel)Īlt, E, S, F: Paste Formulas (Ctrl + ⌘ + V, F in Mac Excel)Īlt, E, S, V: Paste Values (Ctrl + ⌘ + V, V in Mac Excel) Useful Functions for Cleaning Data in Excel:Īlt, A, E: Text to Columns function (No equivalent in Mac Excel) Shift + Ctrl + Arrow Keys: Select to Boundary (same in Mac Excel) Shift + Spacebar: Select Row (same in Mac Excel) To do that, we will use the following functions and shortcuts: Navigation, Selection, and Row/Column Shortcuts:Ĭtrl + Spacebar: Select Column (same in Mac Excel) We want to separate the entire “Address” for each order into Street Address, City, State, and ZIP Code fields: Our task here is simple: we have customer order data, but the addresses are not presented in an ideal format. How to Clean Data in Excel, Part 1: Typical Tasks, Functions, and Shortcuts

Best excel for mac tutorial full#

We do cover Mac Excel shortcuts in our full Excel & Financial Modeling Fundamental course, but they’re presented in completely separate sets of lessons.īelow, we will list the key PC/Windows shortcuts as well as Mac alternatives. NOTE: This tutorial on how to clean data in Excel uses the PC/Windows version shortcuts.