From the course: Excel Weekly Challenge
Unlock this course with a free trial
Join today to access over 24,000 courses taught by industry experts.
Payment errors, part 2 - Microsoft Excel Tutorial
From the course: Excel Weekly Challenge
Payment errors, part 2
- [Instructor] All right, brace yourself. We got a lot to do here, all right? But it's okay, it's okay. We need to figure out what each parent owes. And we have payments over on this sheet. Let's go back. So the first tip, using the data on the payments page, fill in the paid amounts over here. And what's happening, let me clarify something for you. Look at like Chester. Chester has made two different payments and they total $180. We want to see 180 over here. We don't want to see two $90 payments for Chester. All right. Once we have the paid column filled in, then write a formula to calculate what's due. Next, create a pivot table to verify our work. And make sure that everything is captured because this data is not set up to automatically pull a whole data set to create a pivot table. And then finally, add a $35 payment for Chester and refresh to update everything, all right? So pause that, take a deep breath.…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
- 02_07_Create_a_chart_of_monthly_complaints.zip
- 01_05_Identify_and_move_attendee_data.zip
- 02_08_Calculate_pay_based_on_hours_and_location.zip
- 01_06_Combine_first_last_names.zip
- 02_09_Review_accuracy_of_invoice.zip
- 01_03_Align_and_adjust_text_on_an_order_form.zip
- 02_10_Combine_3_donations_lists_into_1.zip
- 01_04_Get_a_count_of_possible_venues.zip
- 01_01_Add_new_participants_to_teams.zip
- 01_02_Format_the_cells_in_a_form.zip
- 01_12_Excel_in_Real_Life_1_House_Shopping.zip
- 01_08_Fill_in_categories_and_sort.zip
- 01_09_Add_a_new_list_and_line_it_up.zip
- 01_07_Clean_up_bizarre_formatting.zip
- 02_11_Group_and_sort_inventory_items.zip
- 02_12_The_expense_report_is_wrong.zip
- 02_13_Categorize_the_status_of_each_apartment.zip
- 02_14_Excel_in_real_life_-_Auditing_inventory.zip
- 01_10_Sort_Addresses_by_Region.zip
- 01_11_Help_find_our_perfect_house.zip
- 02_04_Correct_city_names.zip
- 02_05_Fix_the_pricing_formula.zip
- 02_06_Fill_in_pick-list_details.zip
- 02_02_List_and_inspect_survey_results.zip
- 02_03_Create_table_of_course_summaries.zip
- 02_01_Prepare_this_report_to_be_printed.zip
- 03_01_Leading_zeroes.zip
- 03_02_Stack_and_sort.zip
- 03_03_Fix_dates.zip
- 03_04_Add_a_shape.zip
- 03_05_Combo_chart_with_secondary_axis.zip
- 03_06_Horizonal_sort.zip
- 03_07_Setup_and_format_workbook.zip
- 03_08_Count_addresses.zip
- 03_09_Fix_IDs.zip
- 03_10_Protect_and_validate.zip
- 03_11_Find_duplicate_records.zip
- 03_12_Hide_zeroes.zip
- 03_13_Correct_these_calculations.zip
- 04_01_What_is_this_formula_doing.zip
- 04_02_Pricing_grid_and_dropdown_list.zip
- 04_03_Write_a_formula_trigger.zip
- 04_04_Merge_and_compare_lists.zip
- 04_05_Recreate_this_graphic.zip
- 04_06_Insert_happy_or_sad_face.zip
- 04_07_Conditional_format_and_sort_icons.zip
- 04_08_Crop_and_print.zip
- 04_09_Quick_access_and_theme.zip
- 04_10_Update_graphs.zip
- 04_11_Insert_a_pivot_table.zip
- 04_12_Delete_transactions_and_insert_pivot_table.zip
- 05_01_Close_holes_in_a_list.zip
- 05_02_QAT_and_Pivot_Table.zip
- 05_03_Unpivot_and_split_columns.zip
- 05_04_Presentations_Part_1_Randomize.zip
- 05_05_Presentations_Part_2_What_day_is_each_person_presenting.zip
- 05_06_Calculate_team_fees.zip
- 05_07_Lookup_and_correct_errors.zip
- 05_08_Calculate_final_cost.zip
- 05_09_Clear_excess_images.zip
- 05_10_Format_workbook_defaults.zip
- 05_11_Extract_all_Saturdays.zip
- 06_01.zip
- 06_01_Copy_to_a_new_workbook_and_save_as_PDF.zip
- 06_02_Insert_pivot_table_and_slicers_to_review_workshops.zip
- 06_03_Use_Find_Replace_to_correct_names.zip
- 06_04_Isolate_addresses.zip
- 06_05_Payment_errors_part_1.zip
- 06_06_Payment_errors_part_2.zip
- 06_07_Change_chart_type_and_create_summary.zip
- 06_08_Create_dropdown_lists.zip
- 06_09_Explain_this_sales_formula.zip
- 06_10_Using_COUNTIFS_with_wildcards.zip
- 06_11_Create_an_index_column.zip
- 06_12_Create_tables_and_eliminate_hyperlinks.zip
- 06_13_List_the_top_7_donation_amounts.zip
- 07_01_Get_data_ready_for_a_pivot_table.zip
- 07_02_Modify_this_pivot_table.zip
- 07_03_Power_query_result.zip
- 07_04_Create_a_dynamic_dropdown_list.zip
- 07_05_Dynamic_array_filter_venues.zip
- 07_06_Find_and_restore_the_missing_data.zip
- 07_07_Override_names.zip
- 07_08_Quiz.zip
- 07_09_Protect_formulas_with_a_shape.zip
- 07_10_Show_percentages.zip
- 07_11_Replace_highlights.zip
- 07_12_Replace.zip
- 07_13_Add_a_spin_button.zip
- 08_01_Create_a_custom_data_validation_alert.zip
- 08_02_Modify_Data_Validation.zip
- 08_03_Identify_multiple_shifts.zip
- 08_04_Conditional_Formatting_Icons.zip
- 08_05_Two_windows.zip
- 08_06_Correct_dates.zip
- 08_07_Remove_image_background.zip
- 08_08_Calculate_reimbursements.zip
- 08_09_Fix_and_sort.zip
- 08_10_Apply_formulas.zip
- 08_11_Clear_all_content_and_formatting.zip
- 08_12_Transpose_and_format_data.zip
- 09_01_Find_3_ways_to_extract_this_data.zip
- 09_02_Write_a_formula_from_scratch.zip
- 09_03_Isolate_data_by_the_week.zip
- 09_04_Arts_and_Crafts.zip
- 09_05_Explain_and_test_this_formula.zip
- 09_06_Please_fix_this_pivot_table.zip
- 09_07_Calculate_PTO_Available.zip
- 09_08_Flight_Counts.zip
- 09_09_Adjust_layout_and_set_conditional_formatting.zip
- 09_10_Sum_the_salmon_dishes.zip
- 09_11_Event_Planning_Formula.zip
- 10_01_Payroll.zip
- 10_02_Format_grid.zip
- 10_03_Conditional_Formatting.zip
- 10_04_Guest_Count.zip
- 10_05_Footers.zip
- 10_06_Exams.zip
- 10_07_Slicers.zip
- 10_08_Survey.zip
- 10_09_Extract_Names.zip
- 10_10_Change_the_colors.zip
- 10_11_Chart_Axis.zip
- 10_12_Extract_Sales_Data.zip
- 11_01_Order_form.zip
- 11_02_Correct_and_format_dates.zip
- 11_03_Curriculum_audit.zip
- 11_04_Create_a_running_total.zip
- 11_05_Convert_liquids.zip
- 11_06_Office_expenses.zip
- 11_07_If.zip
- 11_08_Top_Bottom.zip
- 11_09_Calculate_team.zip
- 11_10_Topics_for_Students.zip
- 11_11_Shapes.zip
- 11_12_Hot_or_cold.zip
- 12_01_OneCell.zip
- 12_02_Sales_Data.zip
- 12_03_Unique.zip
- 12_04_Repeat.zip
- 12_05_Three_Lists.zip
- 12_06_Dropdown.zip
- 12_07_Goal_Actual.zip
- 12_08_Deadlines.zip
- 12_09_Icons.zip
- 12_10_Unpivot.zip
- 12_11_Count_Characters.zip
- 12_12_Filter_Project.zip
- 13_01_Sort_Left_Right_Up_Down.zip
- 13_02_Days_Open.zip
- 13_03_Clean_this_list.zip
- 13_04_Sort_High_School_Data.zip
- 13_05_Area_Chart.zip
- 13_06_Fishing_Pivot_Table.zip
- 13_07_Retrieve_prices.zip
- 13_08_Clean_and_sort_this_song_list.zip
- 13_09_Highlight_southwest.zip
- 13_10_Dropdown_from_Table_Headers.zip
- 13_11_Summarize_your_progress.zip
- 14_01_This_printout_is_weird.zip
- 14_02_Adjust_the_due_dates.zip
- 14_03_Append_these_3_datasets.zip
- 14_04_Filter_projects.zip
- 14_05_Merge_columns_2_ways.zip
- 14_06_Who_gets_paid.zip
- 14_07_Build_a_statement.zip
- 14_08_Spin_buttons.zip
- 14_09_Count_Functions.zip
- 14_10_Summarize_and_create_chart.zip
- 14_11_This_lookup_doesnt_work.zip
- 14_12_Slicers.zip
- 15_01_10_rounds_2_teams_who_won.zip
- 15_02_Calculate_Kates_maximum_advance.zip
- 15_03_Assign_Emails.zip
- 15_04_Identify_5_problems.zip
- 15_05_Monitor_capacity.zip
- 15_06_Correct_the_printing_of_this_document.zip
- 15_07_Revenue_Share.zip
- 15_08_Dropdown_lists_chairs_and_prices.zip
- 15_09_Pivot_table_training.zip
- 15_10_Formula_triggers_and_totals.zip
- 15_11_Identify_Multi_part_names.zip
- 15_12_Format_the_times_and_calculate_pay.zip
- 16_01_Area_chart.zip
- 16_02_Create_a_pivot_table_for_the_juniors_and_seniors.zip
- 16_03_Please_fix_this_pivot_table.zip
- 16_04_Picture_formatting_to_improve_a_presentation.zip
- 16_05_Split_the_string_of_IDs.zip
- 16_06_Clean_the_pivot_table_data.zip
- 16_07_Automated_color_changing_emoji.zip
- 16_08_Missing_inventory.zip
- 16_09_Find_the_cars.zip
- 16_10_Spell_out_the_months_and_sort.zip
- 16_11_Count_the_empty_cells.zip
- 16_12_Relative_cells_references_and_pricing.zip
- 17_01_Change_gridlines.zip
- 17_02_Modify_this_2_way_lookup_formula.zip
- 17_03_PJ_10_mile_run.zip
- 17_04_Calculate_commissions_based_on_agreement.zip
- 17_05_Correct_the_XLOOKUP.zip
- 17_06_Airport_Count.zip
- 17_07_Modify_the_Conditional_Formatting.zip
- 17_08_Out_of_pocket_expenses.zip
- 17_09_Identify_the_eastside_apartments.zip
- 17_10_Make_Room_Assignments.zip
- 18_01_Sort_names_in_Power_Query.zip
- 18_02_Identify_the_non_donors.zip
- 18_03_Sort_the_strikethroughs.zip
- 18_04_Simplify_the_IF.zip
- 18_05_Total_the_emoji_scores.zip
- 18_06_Delete_unneeded_rows.zip
- 18_07_Day_and_Time.zip
- 18_08_Attendance_matrix.zip
- 18_09_Measure_the_response_rate.zip
- 18_10_Sort_city_dates.zip
- 18_11_Attendance_queries.zip
- 18_12_Decode_the_words.zip
- 19_01_How_much_is_owed_for_the_beach_house.zip
- 19_02_Get_data_from_an_image.zip
- 19_03_Quickly_answer_these_questions..zip
- 19_04_Flag_and_Delete.zip
- 19_05_Delete_rows.zip
- 19_06_Project_cost.zip
- 19_07_Retrieve_the_name_and_genre.zip
- 19_08_Generate_random_codes.zip
- 19_09_Count_the_letter_L.zip
- 19_10_Double_headers.zip
- 19_11_Quiz.zip
- 19_12_Calculate_total_loss.zip
- 20_01_Calculate_the_percent_change.zip
- 20_02_Times_and_penalties.zip
- 20_03_Build_your_own_Pivot_Table.zip
- 20_04_Sort_by_Last_Name.zip
- 20_05_List_all_of_the_colors.zip
- 20_06_Build_a_Venn_diagram.zip
- 20_07_Event_Calculator.zip
- 20_08_Sum_the_net_loss.zip
- 20_09_Find_the_missing_numbers.zip
- 20_10_Bring_contents_into_a_single_cell.zip
- 20_11_Recycle_a_query_in_Power_Query.zip
- 20_12_Paydays_and_Holidays.zip
- 21_01_Applying_slicers.zip
- 21_02_Build_a_bar_chart.zip
- 21_03_Boxes_on_a_shelf.zip
- 21_04_Insert_rows.zip
- 21_05_Trivia_night_calendar.zip
- 21_06_Tally_top_scores.zip
- 21_07_Filepath.zip
- 21_08_Add_picture_in_pivot_table.zip
- 21_09_Indent_cells.zip
- 21_10_Change_cell_borders.zip
- 21_11_Make_a_logo.zip
- 21_12_Convert_performer_roster.zip
- 22_01_Randomize_Names_3_Ways.zip
- 22_02_Teach_Me_XLOOKUP.zip
- 22_03_Book_Club_Mavis_and_Benedict.zip
- 22_04_Book_Club_Attendance.zip
- 22_05_Create_a_block_of_numbers.zip
- 22_06_Conditional_Formatting_Scores.zip
- 22_07_Sort_by_Shift.zip
- 22_08_Set_Print_Layout.zip
- 22_09_I_Know_The_Day_But_Need_The_Year.zip
- 22_10_Dropdown_and_Filter_Cars.zip
- 22_11_Sort_Pairs_of_Columns.zip
- 22_12_Workshop_Trends.zip
- 23_01_CF_Highlight_Every_3rd_cell.zip
- 23_02_Explain_The_Query.zip
- 23_03_Modify_the_Query.zip
- 23_04_List_the_Cheeses.zip
- 23_05_Schedule_The_Meetings.zip
- 23_06_Append_3_Datasets.zip
- 23_07_Filter_By_3_Criteria.zip
- 23_08_Convert_From_Range_to_Table_References.zip
- 23_09_Control_Worksheet_Entries.zip
- 23_10_Flip_This_Dataset.zip
- 23_11_Build_A_Table.zip
- 23_12_Sort_By_Last_Name.zip
- Flip_the_Zone.zip
- Payment_Tracking.zip
- Home_Scores.zip
- Graph_the_Population.zip
- Win_Loss_Record.zip
- Round_Winners.zip
- November_Advances.zip
- Revenue_Share.zip
- Color_Choices.zip
- Find_the_Top_3.zip
- Calculate_Overtime.zip
- Lineup_Order.zip
- 25_1_Numbers_Adding_Up_to_11.zip
- 25_2_Check_Progress.zip
- 25_3_Find_Valerie.zip
- 25_4_Make_Names.zip
Contents
-
-
-
-
-
-
-
-
-
(Locked)
Copy to a new workbook and save as a PDF2m 1s
-
(Locked)
Insert a PivotTable and slicers to review workshop data3m 23s
-
(Locked)
Use Find and Replace to correct names1m 35s
-
(Locked)
Isolate addresses4m 37s
-
(Locked)
Payment errors, part 11m 39s
-
(Locked)
Payment errors, part 24m 32s
-
(Locked)
Change chart type and create summary3m 33s
-
(Locked)
Create drop-down lists2m 59s
-
(Locked)
Explain this sales formula3m 50s
-
(Locked)
Using COUNTIFS with wildcards1m 42s
-
(Locked)
Create an index column1m 32s
-
(Locked)
Create tables and eliminate hyperlinks2m 17s
-
(Locked)
List the top seven donation amounts1m 45s
-
(Locked)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-