Bring a large pot of water to a boil and cook the elbow macaroni until just tender, 8–10 minutes; drain, rinse under cold water until cool, 1–2 minutes, and drain thoroughly.
In a large mixing bowl, whisk together the mayonnaise, apple cider vinegar, yellow mustard, sugar, celery seed, kosher salt, and black pepper until smooth, about 30 seconds.
Add the cooled pasta, celery, red bell pepper, red onion, carrot, and sweet pickle relish to the bowl and toss until evenly coated, 1–2 minutes.
Cover and chill until the flavors meld and the pasta absorbs some dressing, at least 1 hour (up to 24 hours).
Stir well before serving; the salad should be creamy, well-coated, and pleasantly tangy when cold.