https://bit.ly/3HHWQME
We respect your privacy. Unsubscribe at any time.
Prefer reading? The same steps are written out below the video.
Seven real data-cleaning jobs handed to ChatGPT, each ending in data or a formula you paste back into Excel. Open ChatGPT in your browser next to the workbook. The general rule: say where the data is, what you want, and what format the answer should come in.
Copy the messy date column (A2:A15) and ask: Format all the following date data so it is consistent. Output the data in a table. Press Shift+Enter for a new line, paste the dates and send. For long lists, follow up with Output the second column in CSV format and use the copy button, then paste the result into Excel.
Copy the values (D2:D16) and ask for an Excel formula to standardise this data so the output is Yes, No or Unknown, spelling out the mapping (y becomes Yes, n becomes No, unk becomes Unknown). If the formula uses A1, tell it your data starts in D2. Paste the formula into E2 and fill down.
Copy a list of links and ask: Extract the root URL and the article or blog title from this data set. Output the results in two columns. Ask for sentence case if the titles need tidying, then paste the two columns into Excel.
For a code like V1-BN20-D001, paste one example, explain its three parts (version, model, item number) and ask for three Excel formulas, one per part, with the reference cell A2. Paste each formula into its column and fill down.
Copy the service notes (D2:D14) and ask whether each one needs a follow-up visit: Classify each item as Yes or No and output the results in a table. Paste the table back, using Match Destination Formatting if you want to keep your sheet’s look, and check any borderline cases by hand.
Ask for a formula to classify ages into generations (Gen Z, Millennial, Baby Boomer and so on), giving the age range in your data (19 to 88) and the reference cell (B2). If it answers with birth years instead of ages, ask it to switch. Paste into C2 and fill down.
Ask what fields a demographic survey might have, then: Create a dummy data set for a survey with these fields: name, education level, employment status, income level, residential location and nationality. The survey takes place in the United Kingdom. 15 rows. Paste the table into A1. It’s ideal for testing charts and models without real personal data.
My Excel Power Query, Power Pivot, DAX & Power 3D Map course cleans the same file every month with one click, where ChatGPT does a one-off.
WATCHING ON YOUTUBE?
11 step-by-step courses on Excel, Power BI, Python and AI, all on Udemy with lifetime access.
